CREATE OR REPLACE PACKAGE apps.xxcofi_mer_sold_not_del_pkg
AS
/* --------------------------------------------------------------------------------
Program Name xxcofi_mer_sold_not_del_pkg
TYPE PLSQL Package
Input Parms
Output Parms
Table Access
oe_order_headers_all --select
oe_order_lines_all --select
mtl_system_items_b --select
hr_organization_units --select
hr_locations_all --select
mtl_item_categories_v --select
fnd_lookup_values --select
ar_cash_receipts_all --select
oe_ship_methods_v --select
cst_item_costs --select
cst_cost_types --select
mtl_parameters --select
jtf_rs_salesreps --select
po_vendors --select
po_approved_supplier_list --select
AUTHOR Chandra Sekhar kadali
DATE 22-Dec-2009
VERSION 1.0
DESCRIPTION This package queries all the merchandise items which are sold
but not yet delivered.
It is built bases on the existing report xxcofi_rpt097
----------------------------------------------------------------------------------
DATE AUTHOR VERSION REASON
-------------------------------------------------------------------------------
22-Dec2009 Chandu 1.0 Inital version
----------------------------------------------------------------------------------*/
PROCEDURE main (errbuf OUT VARCHAR2, retcode OUT VARCHAR2);
PROCEDURE setstandalonemode (p_status IN BOOLEAN);
PROCEDURE write_log (p_content IN VARCHAR2);
PROCEDURE write_error_record (p_errmsg IN VARCHAR2);
PROCEDURE write_detail_record (p_content IN VARCHAR2);
FUNCTION getonhandqty (p_org_id IN NUMBER,p_item_id IN NUMBER) RETURN NUMBER;
END xxcofi_mer_sold_not_del_pkg;
/
CREATE OR REPLACE PACKAGE BODY apps.xxcofi_mer_sold_not_del_pkg
IS
/* -----------------------------------------------------------------------------------
Program Name xxcofi_mer_sold_not_del_pkg
TYPE PLSQL Package
Input Parms
Output Parms
Table Access
oe_order_headers_all --select
oe_order_lines_all --select
mtl_system_items_b --select
hr_organization_units --select
hr_locations_all --select
mtl_item_categories_v --select
fnd_lookup_values --select
ar_cash_receipts_all --select
oe_ship_methods_v --select
cst_item_costs --select
cst_cost_types --select
mtl_parameters --select
jtf_rs_salesreps --select
po_vendors --select
po_approved_supplier_list --select
AUTHOR Chandra Sekhar kadali
DATE 22-Dec-2009
VERSION 1.0
DESCRIPTION This package queries all the merchandise items which are sold but not
yet delivered. It is built bases on the existing report xxcofi_rpt097
------------------------------------------------------------------------------------
DATE AUTHOR VERSION REASON
------------------------------------------------------------------------------------
22-Dec2009 Chandu 1.0 Inital version
------------------------------------------------------------------------------------*/
/* local variables declaration */
/* l_file_hd1 is a UTL File Record Type */
l_file_hd1 UTL_FILE.file_type;
/* l_err_location is used for maintaining error log */
l_err_location VARCHAR2 (80);
/* l_standalone is used for maintaing boolean values based on l_standalone mode */
l_standalone BOOLEAN := FALSE;
/* Temporary varibale for writing Header Details */
l_tempstr VARCHAR2 (200);
PROCEDURE setstandalonemode (p_status IN BOOLEAN)
IS
/* This procedure is used to Set the standalone mode */
BEGIN
l_standalone := p_status;
END;
PROCEDURE write_detail_record (p_content IN VARCHAR2)
IS
/*If the mode is not standalone then this procedure is used for writing
output to an UTL File.Otherwise it will write into an fnd output file */
BEGIN
IF UTL_FILE.is_open (l_file_hd1)
THEN
UTL_FILE.put_line (l_file_hd1, p_content);
END IF;
IF NOT l_standalone
THEN
fnd_file.put_line (fnd_file.output, p_content);
END IF;
END;
PROCEDURE write_log (p_content IN VARCHAR2)
IS
/*This procedure is used for writing fnd log messages. We will write the report header details, record count
and other meaningful details. In any case if the program is running without the concurrent manager.
we will display the log file in FND log. */
BEGIN
IF l_standalone
THEN
/* If the program is running without concurrent manager, */
/* send the log message to screen. */
DBMS_OUTPUT.put_line (p_content);
ELSE
fnd_file.put_line (fnd_file.LOG, p_content);
END IF;
END;
PROCEDURE write_error_record (p_errmsg IN VARCHAR2)
IS
/********************************************************************/
/* This procedure write an error message into the output file */
/* (either master or detail output file, depends on the value */
/* of the flag err_source_flag. It also writes the message */
/* the log file. */
/********************************************************************/
BEGIN
write_detail_record (' '); /* Produce blank line. */
write_detail_record (p_errmsg);
write_detail_record (' '); /* Produce blank line. */
/* Write the same message into the log file. */
write_log (p_errmsg);
END;
FUNCTION getonhandqty (p_org_id NUMBER, p_item_id NUMBER)
RETURN NUMBER
IS
/*This function is used for calculating on hand quantity of a particular item in a particular organization.
We will use this value to find out the ¿in stock¿ and ¿on order¿ details. */
l_qty NUMBER;
BEGIN
BEGIN
SELECT SUM (moq.transaction_quantity)
INTO l_qty
FROM apps.mtl_onhand_quantities moq
WHERE moq.organization_id = p_org_id
AND moq.inventory_item_id = p_item_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
write_log (' No data found in transaction quantity query');
WHEN OTHERS
THEN
write_log ( ' No data found in transaction quantity query'
|| SQLERRM
);
END;
IF l_qty IS NULL
THEN
l_qty := 0;
END IF;
RETURN l_qty;
END;
PROCEDURE main (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
/* Main Procedure will call all the other procedures or functions.Here
we willl oepn a cursor and we write the report details into a UTL file */
l_report_name VARCHAR2 (50) := 'XXCOFI_RPT097';
l_report_title VARCHAR2 (100) := 'COFI Merchandise Sold Not Delivered Split Report';
l_err_ret VARCHAR2 (255);
l_record_no NUMBER := 0;
l_top VARCHAR2 (200) := NULL;
l_tempstr VARCHAR2 (100);
/******************************************************************************/
/* This is the cursor for building the report. */
/* */
/* Remark: */
/* */
/* oh.attribute1 is the store to sell the merchandise. */
/* */
/* ol.attribute7 store the receipt_id where it is not null if the */
/* merchandise is paid, this generate an account receivable record in AR. */
/* Column attribute7 store the receipt_id to point to the record created */
/* in account receivable. */
/* */
/* POS Receipt = 0 is added in order to show records */
/* came from converted orders, those orders have attribute7 populated */
/* by a zero. This condition is achieved by changing the condition for */
/* linking with the acr_cash_receipts_all to be outer join, this will allow*/
/* to show record with ol.attribute7 = 0, do not just put in the condition */
/* ol.attribute7 = 0, this will return with a lot of records. */
/* */
/* To determine whether the merchandises are delivered or picked up by the */
/* customers, the flow_status_code is used to serve this purpose. */
/* */
/* If the merchandise was picked up by the customer, the flow_status_code */
/* would be Shipped (temporary status) or Closed. The status will indicate*/
/* that the item has not yet been picked up by the customer. */
/******************************************************************************/
CURSOR cur_master
IS
SELECT DECODE
(DECODE
(INSTR
(TO_CHAR
(DECODE
(NVL
(apps.xxcofi_mer_sold_not_del_pkg.getonhandqty
(cic.organization_id,
cic.inventory_item_id
),
0
),
0, -1,
apps.xxcofi_mer_sold_not_del_pkg.getonhandqty
(cic.organization_id,
cic.inventory_item_id
)
)
),
'-'
),
0, DECODE (INSTR (osmv.meaning, 'Home'),
0, '1.1',
'2.1'
),
DECODE (INSTR (osmv.meaning, 'Home'),
0, '1.2',
'2.2'
)
),
1.1, 'Customer Not Pick Up (In Stock)',
2.1, 'Home Delivry (In Stock)',
3.1, 'Delivered - No delivery confirmed from Matrix',
NULL
) in_stock
,DECODE
(DECODE
(INSTR
(TO_CHAR
(DECODE
(NVL
(apps.xxcofi_mer_sold_not_del_pkg.getonhandqty
(cic.organization_id,
cic.inventory_item_id
),
0
),
0, -1,
apps.xxcofi_mer_sold_not_del_pkg.getonhandqty
(cic.organization_id,
cic.inventory_item_id
)
)
),
'-'
),
0, DECODE (INSTR (osmv.meaning, 'Home'),
0, '1.1',
'2.1'
),
DECODE (INSTR (osmv.meaning, 'Home'),
0, '1.2',
'2.2'
)
),
1.2, 'Customer Not Pick Up (On Order)',
2.2, 'Home Delivry (On Order)',
NULL
) on_order
,DECODE (SUBSTR (loc.tax_name, 1, 3),
'Bay', 'Bay',
'Zel', 'Zellers',
'KMT', 'Kmart'
) banner
,hr_sold.name store
,mic.segment4 category
,mic.segment1 gma
,mic.segment2 group_name
,mic.segment3 dept
,msib.segment1 item
,msib.description item_description
,pov.segment1 vendor_number
,pov.vendor_name vendor_name
,oh.order_number sales_order_no
,srep.name sales_associate
,ol.flow_status_code status
,osmv.meaning shipping_method
,mp.organization_code fulfillment_location
,acr.receipt_date pos_processing_date
,ol.schedule_ship_date promised_date
,(SELECT SUBSTR (period_name, 5, 6)|| '-'|| SUBSTR (period_name, 1, 3)
FROM gl_periods
WHERE period_set_name = 'HBC'
AND TRUNC (ol.schedule_ship_date) BETWEEN start_date AND end_date) financial_period
,SUM (ol.ordered_quantity) quantity
,msib.list_price_per_unit item_cost
,SUM (ol.ordered_quantity)*(NVL (cic.item_cost, 0)) total_frozen_cost
,SUM (ol.ordered_quantity)*(ol.unit_list_price) total_list_price
,SUM (ol.ordered_quantity)*(ol.unit_selling_price) total_sales_price
,((SUM (ol.ordered_quantity)*ol.unit_list_price)
- (SUM (ol.ordered_quantity)*ol.unit_selling_price)) provisional_md
FROM oe_order_headers_all oh,
oe_order_lines_all ol,
mtl_system_items_b msib,
hr_organization_units hr_sold,
hr_locations_all loc,
mtl_item_categories_v mic,
fnd_lookup_values flv,
ar_cash_receipts_all acr,
oe_ship_methods_v osmv,
cst_item_costs cic,
cst_cost_types cct,
mtl_parameters mp,
jtf_rs_salesreps srep,
po_vendors pov,
po_approved_supplier_list sl
WHERE oh.header_id = ol.header_id
AND ol.salesrep_id = srep.salesrep_id(+)
AND srep.org_id = 22
AND cic.cost_type_id = cct.cost_type_id
AND cct.cost_type = 'Frozen'
AND ol.inventory_item_id = cic.inventory_item_id
AND oh.attribute1 = cic.organization_id
AND ol.inventory_item_id = msib.inventory_item_id
AND oh.attribute1 = msib.organization_id
AND ol.inventory_item_id = mic.inventory_item_id
AND oh.attribute1 = mic.organization_id
AND ol.line_category_code = 'ORDER'
AND mic.category_set_id = 1
AND oh.attribute1 = hr_sold.organization_id
AND loc.location_id = hr_sold.location_id
AND ol.ship_from_org_id = mp.organization_id
AND ol.attribute7 IS NOT NULL
AND (ol.attribute7 = acr.cash_receipt_id(+))
AND ol.flow_status_code IN(
'AWAITING_FULFILLMENT','AWAITING_SHIPPING',
'PICKED', 'BOOKED', 'PICKED PARTIAL'
)
AND flv.lookup_type = 'ITEM_TYPE'
AND flv.language = USERENV ('LANG')
AND flv.attribute1 = 'Merchandise'
AND flv.lookup_code = msib.item_type
AND osmv.lookup_code = ol.shipping_method_code
AND (osmv.meaning LIKE 'Customer-Pickup%' OR osmv.meaning LIKE 'Home Delivery%')
AND msib.inventory_item_id = sl.item_id
AND sl.vendor_id = pov.vendor_id
AND sl.attribute5 = 'Primary'
AND TRUNC (NVL (acr.receipt_date , oh.ordered_date)) <= TRUNC (SYSDATE)
--To display the records for which the receipt date is less than or equal to system date
AND (sl.disable_flag IS NULL OR sl.disable_flag = 'N')
--To display active vendor sites only
GROUP BY hr_sold.name,
mic.segment4,
msib.segment1,
msib.inventory_item_id,
oh.order_number,
srep.name,
ol.flow_status_code,
osmv.meaning,
mp.organization_code,
acr.receipt_date,
ol.schedule_ship_date,
cic.item_cost,
msib.list_price_per_unit,
cic.organization_id,
cic.inventory_item_id,
ol.ordered_quantity,
ol.unit_list_price,
ol.unit_selling_price,
DECODE (SUBSTR (loc.tax_name, 1, 3),
'Bay', 'Bay',
'Zel', 'Zellers',
'KMT', 'Kmart'
),
mic.segment1,
mic.segment2,
mic.segment3,
msib.description,
pov.segment1,
pov.vendor_name
ORDER BY 1, 2, 5, 3, 4;
BEGIN
l_err_location := 'Error in reading profile variable';
/* l_top contains the outbound path */
fnd_profile.get ('XXCOFIDATA_TOP', l_top);
IF l_top IS NULL OR l_top = ''
THEN
l_top := '/cdv01/oracle/cdvappl/xxcofidata/1.0.0/outbound';
ELSE
l_top := l_top || '/outbound';
END IF;
/********************************************************/
/* Open/create Output to build report in CSV format. */
/********************************************************/
l_err_location := 'Error in open output file';
l_file_hd1 :=
UTL_FILE.fopen (l_top,
l_report_name
|| '_'
|| TO_CHAR (SYSDATE, 'YYYYMMDDHH24MI')
|| '.csv',
'w'
);
/* Put start message in the concurrent manager log file. */
/************************************************************/
write_log (' ');
write_log ('START RUNNING ' || l_report_name || ' REPORT PROGRAM.');
write_log ('-***-');
write_log (' ');
write_log ('Date used in generating this report: ' || SYSDATE);
write_log (' ');
/********************************************************/
/* Begin to generate CSV files */
/********************************************************/
write_log ('Outputing ' || l_report_name || '*.csv FILE TO ' || l_top);
write_log ('START GENERATING REPORT...');
/* Report Column Header Details */
l_tempstr :=
'COFI Merchandise Sold Not Delivered Split Report,,,,,,,,,,,,,'
|| SYSDATE;
write_detail_record (l_tempstr);
l_tempstr := 'For Month End:,,' || TO_CHAR (SYSDATE, 'DD-MON-YYYY');
write_detail_record (l_tempstr);
write_detail_record ( 'In_Stock'
|| ','
|| 'On_Order'
|| ','
|| 'Banner'
|| ','
|| 'Store'
|| ','
|| 'Category'
|| ','
|| 'GMA'
|| ','
|| 'Group_Name'
|| ','
|| 'DEPT'
|| ','
|| 'Item'
|| ','
|| 'Item_Description'
|| ','
|| 'Vendor_Number'
|| ','
|| 'Vendor_Name'
|| ','
|| 'Sales_Order_No'
|| ','
|| 'Sales_Associate'
|| ','
|| 'Status'
|| ','
|| 'Shipping_Method'
|| ','
|| 'Fulfillment_Location'
|| ','
|| 'POS_Processing_Date'
|| ','
|| 'Promised_Date'
|| ','
|| 'Financial_Period'
|| ','
|| 'Quantity'
|| ','
|| 'Item_Cost'
|| ','
|| '"'
|| 'Total_Frozen_Cost'
|| '",'
|| '"'
|| 'Total_List_Price'
|| '",'
|| '"'
|| 'Total_Sales_Price'
|| '",'
|| '"'
|| 'Provisional_MD'
|| '"'
);
/* Make record count as zero */
l_record_no := 0;
/* Start writing record column details to UTL File */
l_err_location := 'Error in retrieving rows from database';
FOR out_rec IN cur_master
LOOP
l_err_location := 'Error in writing detail record';
write_detail_record ( out_rec.in_stock
|| ','
|| out_rec.on_order
|| ','
|| out_rec.banner
|| ','
|| out_rec.store
|| ','
|| out_rec.category
|| ','
|| out_rec.gma
|| ','
|| out_rec.group_name
|| ','
|| out_rec.dept
|| ','
|| out_rec.item
|| ','
|| out_rec.item_description
|| ','
|| out_rec.vendor_number
|| ','
|| out_rec.vendor_name
|| ','
|| out_rec.sales_order_no
|| ','
|| '"'
|| out_rec.sales_associate
|| '"'
|| ','
|| out_rec.status
|| ','
|| out_rec.shipping_method
|| ','
|| out_rec.fulfillment_location
|| ','
|| TO_CHAR (out_rec.pos_processing_date,
'DD-MON-YYYY'
)
|| ','
|| TO_CHAR (out_rec.promised_date,
'DD-MON-YYYY')
|| ','
|| out_rec.financial_period
|| ','
|| out_rec.quantity
|| ','
|| '"'
|| TO_CHAR (out_rec.item_cost,
'$999,999,999.90')
|| '",'
|| '"'
|| TO_CHAR (out_rec.total_frozen_cost,
'$999,999,999.90'
)
|| '",'
|| '"'
|| TO_CHAR (out_rec.total_list_price,
'$999,999,999.90'
)
|| '",'
|| '"'
|| TO_CHAR (out_rec.total_sales_price,
'$999,999,999.90'
)
|| '",'
|| '"'
|| TO_CHAR (out_rec.provisional_md,
'$999,999,999.90'
)
|| '"'
);
/* Increment the record count */
l_record_no := l_record_no + 1;
END LOOP;
IF l_record_no = 0
THEN
write_log (' ');
write_log ('There are no data available for building the report');
write_log (' ');
write_detail_record (' ');
write_detail_record
('There are no data available for building the report');
write_detail_record (' ');
UTL_FILE.fflush (l_file_hd1);
UTL_FILE.fclose (l_file_hd1);
errbuf := l_report_name || ' REPORT FILE JOB COMPLETED SUCCESSFULLY';
retcode := '0';
/*****************************************************/
/* Output Number Of Record in detail transactions. */
/*****************************************************/
write_log (' ');
write_log ('Total records for report: ' || l_record_no);
write_log (' ');
write_log ('JOB COMPLETED WITH NO DATA AVAILABLE');
fnd_file.CLOSE;
-- (FND_FILE.OUTPUT);
RETURN;
END IF;
UTL_FILE.fflush (l_file_hd1);
UTL_FILE.fclose (l_file_hd1);
errbuf := l_report_name || ' REPORT FILE JOB COMPLETED SUCCESSFULLY';
retcode := '0';
/*****************************************************/
/* Output Number Of Record in detail transactions. */
/*****************************************************/
write_log (' ');
write_log ('Total records for report: ' || l_record_no);
write_log (' ');
write_log ('JOB COMPLETED SUCCESSFULLY');
fnd_file.CLOSE; -- (FND_FILE.OUTPUT);
EXCEPTION
WHEN UTL_FILE.invalid_filehandle
THEN
UTL_FILE.fclose (l_file_hd1);
errbuf := 'INVALID FILE HANDLE';
retcode := '1';
write_error_record ( l_err_location
|| ': '
|| SQLCODE
|| ' AND ERROR is INVALID FILE HANDLE : '
|| SQLERRM
);
fnd_file.CLOSE;
WHEN UTL_FILE.invalid_path
THEN
UTL_FILE.fclose (l_file_hd1);
errbuf := 'INVALID PATH';
retcode := '2';
write_error_record ( l_err_location
|| ': '
|| SQLCODE
|| ' AND ERROR is INVALID PATH : '
|| SQLERRM
);
fnd_file.CLOSE;
WHEN UTL_FILE.invalid_mode
THEN
UTL_FILE.fclose (l_file_hd1);
errbuf := 'INVALID MODE';
retcode := '3';
write_error_record ( l_err_location
|| ': '
|| SQLCODE
|| ' AND ERROR is INVALID MODE : '
|| SQLERRM
);
fnd_file.CLOSE;
WHEN UTL_FILE.invalid_operation
THEN
UTL_FILE.fclose (l_file_hd1);
errbuf := 'INVALID OPERATION';
retcode := '4';
write_error_record ( l_err_location
|| ': '
|| SQLCODE
|| ' AND ERROR is INVALID OPERATION : '
|| SQLERRM
);
fnd_file.CLOSE;
WHEN UTL_FILE.read_error
THEN
UTL_FILE.fclose (l_file_hd1);
errbuf := 'READ ERROR';
retcode := '5';
write_error_record ( l_err_location
|| ': '
|| SQLCODE
|| ' AND ERROR is READ ERROR : '
|| SQLERRM
);
fnd_file.CLOSE;
WHEN UTL_FILE.write_error
THEN
UTL_FILE.fclose (l_file_hd1);
errbuf := 'WRITE ERROR';
retcode := '6';
write_error_record ( l_err_location
|| ': '
|| SQLCODE
|| ' AND ERROR is WRITE ERROR : '
|| SQLERRM
);
fnd_file.CLOSE;
WHEN UTL_FILE.internal_error
THEN
UTL_FILE.fclose (l_file_hd1);
errbuf := 'INTERNAL ERROR';
retcode := '7';
write_error_record ( l_err_location
|| ': '
|| SQLCODE
|| ' AND ERROR is INTERNAL ERROR : '
|| SQLERRM
);
fnd_file.CLOSE;
WHEN NO_DATA_FOUND
THEN
UTL_FILE.fclose (l_file_hd1);
errbuf := 'NO DATA FOUND';
retcode := '8';
write_error_record ( l_err_location
|| ': '
|| SQLCODE
|| ' Error is NO DATA FOUND :'
|| SQLERRM
);
fnd_file.CLOSE;
WHEN OTHERS
THEN
UTL_FILE.fclose (l_file_hd1);
errbuf := 'OTHERS ';
retcode := '9';
write_error_record (l_err_location || ': ' || SQLERRM);
fnd_file.CLOSE;
END;
END xxcofi_mer_sold_not_del_pkg;
--Package xxcofi_mer_sold_not_del_pkg End
/
SHOW ERRORS
--Display Errors Occured
/
EXIT
AS
/* --------------------------------------------------------------------------------
Program Name xxcofi_mer_sold_not_del_pkg
TYPE PLSQL Package
Input Parms
Output Parms
Table Access
oe_order_headers_all --select
oe_order_lines_all --select
mtl_system_items_b --select
hr_organization_units --select
hr_locations_all --select
mtl_item_categories_v --select
fnd_lookup_values --select
ar_cash_receipts_all --select
oe_ship_methods_v --select
cst_item_costs --select
cst_cost_types --select
mtl_parameters --select
jtf_rs_salesreps --select
po_vendors --select
po_approved_supplier_list --select
AUTHOR Chandra Sekhar kadali
DATE 22-Dec-2009
VERSION 1.0
DESCRIPTION This package queries all the merchandise items which are sold
but not yet delivered.
It is built bases on the existing report xxcofi_rpt097
----------------------------------------------------------------------------------
DATE AUTHOR VERSION REASON
-------------------------------------------------------------------------------
22-Dec2009 Chandu 1.0 Inital version
----------------------------------------------------------------------------------*/
PROCEDURE main (errbuf OUT VARCHAR2, retcode OUT VARCHAR2);
PROCEDURE setstandalonemode (p_status IN BOOLEAN);
PROCEDURE write_log (p_content IN VARCHAR2);
PROCEDURE write_error_record (p_errmsg IN VARCHAR2);
PROCEDURE write_detail_record (p_content IN VARCHAR2);
FUNCTION getonhandqty (p_org_id IN NUMBER,p_item_id IN NUMBER) RETURN NUMBER;
END xxcofi_mer_sold_not_del_pkg;
/
CREATE OR REPLACE PACKAGE BODY apps.xxcofi_mer_sold_not_del_pkg
IS
/* -----------------------------------------------------------------------------------
Program Name xxcofi_mer_sold_not_del_pkg
TYPE PLSQL Package
Input Parms
Output Parms
Table Access
oe_order_headers_all --select
oe_order_lines_all --select
mtl_system_items_b --select
hr_organization_units --select
hr_locations_all --select
mtl_item_categories_v --select
fnd_lookup_values --select
ar_cash_receipts_all --select
oe_ship_methods_v --select
cst_item_costs --select
cst_cost_types --select
mtl_parameters --select
jtf_rs_salesreps --select
po_vendors --select
po_approved_supplier_list --select
AUTHOR Chandra Sekhar kadali
DATE 22-Dec-2009
VERSION 1.0
DESCRIPTION This package queries all the merchandise items which are sold but not
yet delivered. It is built bases on the existing report xxcofi_rpt097
------------------------------------------------------------------------------------
DATE AUTHOR VERSION REASON
------------------------------------------------------------------------------------
22-Dec2009 Chandu 1.0 Inital version
------------------------------------------------------------------------------------*/
/* local variables declaration */
/* l_file_hd1 is a UTL File Record Type */
l_file_hd1 UTL_FILE.file_type;
/* l_err_location is used for maintaining error log */
l_err_location VARCHAR2 (80);
/* l_standalone is used for maintaing boolean values based on l_standalone mode */
l_standalone BOOLEAN := FALSE;
/* Temporary varibale for writing Header Details */
l_tempstr VARCHAR2 (200);
PROCEDURE setstandalonemode (p_status IN BOOLEAN)
IS
/* This procedure is used to Set the standalone mode */
BEGIN
l_standalone := p_status;
END;
PROCEDURE write_detail_record (p_content IN VARCHAR2)
IS
/*If the mode is not standalone then this procedure is used for writing
output to an UTL File.Otherwise it will write into an fnd output file */
BEGIN
IF UTL_FILE.is_open (l_file_hd1)
THEN
UTL_FILE.put_line (l_file_hd1, p_content);
END IF;
IF NOT l_standalone
THEN
fnd_file.put_line (fnd_file.output, p_content);
END IF;
END;
PROCEDURE write_log (p_content IN VARCHAR2)
IS
/*This procedure is used for writing fnd log messages. We will write the report header details, record count
and other meaningful details. In any case if the program is running without the concurrent manager.
we will display the log file in FND log. */
BEGIN
IF l_standalone
THEN
/* If the program is running without concurrent manager, */
/* send the log message to screen. */
DBMS_OUTPUT.put_line (p_content);
ELSE
fnd_file.put_line (fnd_file.LOG, p_content);
END IF;
END;
PROCEDURE write_error_record (p_errmsg IN VARCHAR2)
IS
/********************************************************************/
/* This procedure write an error message into the output file */
/* (either master or detail output file, depends on the value */
/* of the flag err_source_flag. It also writes the message */
/* the log file. */
/********************************************************************/
BEGIN
write_detail_record (' '); /* Produce blank line. */
write_detail_record (p_errmsg);
write_detail_record (' '); /* Produce blank line. */
/* Write the same message into the log file. */
write_log (p_errmsg);
END;
FUNCTION getonhandqty (p_org_id NUMBER, p_item_id NUMBER)
RETURN NUMBER
IS
/*This function is used for calculating on hand quantity of a particular item in a particular organization.
We will use this value to find out the ¿in stock¿ and ¿on order¿ details. */
l_qty NUMBER;
BEGIN
BEGIN
SELECT SUM (moq.transaction_quantity)
INTO l_qty
FROM apps.mtl_onhand_quantities moq
WHERE moq.organization_id = p_org_id
AND moq.inventory_item_id = p_item_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
write_log (' No data found in transaction quantity query');
WHEN OTHERS
THEN
write_log ( ' No data found in transaction quantity query'
|| SQLERRM
);
END;
IF l_qty IS NULL
THEN
l_qty := 0;
END IF;
RETURN l_qty;
END;
PROCEDURE main (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
/* Main Procedure will call all the other procedures or functions.Here
we willl oepn a cursor and we write the report details into a UTL file */
l_report_name VARCHAR2 (50) := 'XXCOFI_RPT097';
l_report_title VARCHAR2 (100) := 'COFI Merchandise Sold Not Delivered Split Report';
l_err_ret VARCHAR2 (255);
l_record_no NUMBER := 0;
l_top VARCHAR2 (200) := NULL;
l_tempstr VARCHAR2 (100);
/******************************************************************************/
/* This is the cursor for building the report. */
/* */
/* Remark: */
/* */
/* oh.attribute1 is the store to sell the merchandise. */
/* */
/* ol.attribute7 store the receipt_id where it is not null if the */
/* merchandise is paid, this generate an account receivable record in AR. */
/* Column attribute7 store the receipt_id to point to the record created */
/* in account receivable. */
/* */
/* POS Receipt = 0 is added in order to show records */
/* came from converted orders, those orders have attribute7 populated */
/* by a zero. This condition is achieved by changing the condition for */
/* linking with the acr_cash_receipts_all to be outer join, this will allow*/
/* to show record with ol.attribute7 = 0, do not just put in the condition */
/* ol.attribute7 = 0, this will return with a lot of records. */
/* */
/* To determine whether the merchandises are delivered or picked up by the */
/* customers, the flow_status_code is used to serve this purpose. */
/* */
/* If the merchandise was picked up by the customer, the flow_status_code */
/* would be Shipped (temporary status) or Closed. The status will indicate*/
/* that the item has not yet been picked up by the customer. */
/******************************************************************************/
CURSOR cur_master
IS
SELECT DECODE
(DECODE
(INSTR
(TO_CHAR
(DECODE
(NVL
(apps.xxcofi_mer_sold_not_del_pkg.getonhandqty
(cic.organization_id,
cic.inventory_item_id
),
0
),
0, -1,
apps.xxcofi_mer_sold_not_del_pkg.getonhandqty
(cic.organization_id,
cic.inventory_item_id
)
)
),
'-'
),
0, DECODE (INSTR (osmv.meaning, 'Home'),
0, '1.1',
'2.1'
),
DECODE (INSTR (osmv.meaning, 'Home'),
0, '1.2',
'2.2'
)
),
1.1, 'Customer Not Pick Up (In Stock)',
2.1, 'Home Delivry (In Stock)',
3.1, 'Delivered - No delivery confirmed from Matrix',
NULL
) in_stock
,DECODE
(DECODE
(INSTR
(TO_CHAR
(DECODE
(NVL
(apps.xxcofi_mer_sold_not_del_pkg.getonhandqty
(cic.organization_id,
cic.inventory_item_id
),
0
),
0, -1,
apps.xxcofi_mer_sold_not_del_pkg.getonhandqty
(cic.organization_id,
cic.inventory_item_id
)
)
),
'-'
),
0, DECODE (INSTR (osmv.meaning, 'Home'),
0, '1.1',
'2.1'
),
DECODE (INSTR (osmv.meaning, 'Home'),
0, '1.2',
'2.2'
)
),
1.2, 'Customer Not Pick Up (On Order)',
2.2, 'Home Delivry (On Order)',
NULL
) on_order
,DECODE (SUBSTR (loc.tax_name, 1, 3),
'Bay', 'Bay',
'Zel', 'Zellers',
'KMT', 'Kmart'
) banner
,hr_sold.name store
,mic.segment4 category
,mic.segment1 gma
,mic.segment2 group_name
,mic.segment3 dept
,msib.segment1 item
,msib.description item_description
,pov.segment1 vendor_number
,pov.vendor_name vendor_name
,oh.order_number sales_order_no
,srep.name sales_associate
,ol.flow_status_code status
,osmv.meaning shipping_method
,mp.organization_code fulfillment_location
,acr.receipt_date pos_processing_date
,ol.schedule_ship_date promised_date
,(SELECT SUBSTR (period_name, 5, 6)|| '-'|| SUBSTR (period_name, 1, 3)
FROM gl_periods
WHERE period_set_name = 'HBC'
AND TRUNC (ol.schedule_ship_date) BETWEEN start_date AND end_date) financial_period
,SUM (ol.ordered_quantity) quantity
,msib.list_price_per_unit item_cost
,SUM (ol.ordered_quantity)*(NVL (cic.item_cost, 0)) total_frozen_cost
,SUM (ol.ordered_quantity)*(ol.unit_list_price) total_list_price
,SUM (ol.ordered_quantity)*(ol.unit_selling_price) total_sales_price
,((SUM (ol.ordered_quantity)*ol.unit_list_price)
- (SUM (ol.ordered_quantity)*ol.unit_selling_price)) provisional_md
FROM oe_order_headers_all oh,
oe_order_lines_all ol,
mtl_system_items_b msib,
hr_organization_units hr_sold,
hr_locations_all loc,
mtl_item_categories_v mic,
fnd_lookup_values flv,
ar_cash_receipts_all acr,
oe_ship_methods_v osmv,
cst_item_costs cic,
cst_cost_types cct,
mtl_parameters mp,
jtf_rs_salesreps srep,
po_vendors pov,
po_approved_supplier_list sl
WHERE oh.header_id = ol.header_id
AND ol.salesrep_id = srep.salesrep_id(+)
AND srep.org_id = 22
AND cic.cost_type_id = cct.cost_type_id
AND cct.cost_type = 'Frozen'
AND ol.inventory_item_id = cic.inventory_item_id
AND oh.attribute1 = cic.organization_id
AND ol.inventory_item_id = msib.inventory_item_id
AND oh.attribute1 = msib.organization_id
AND ol.inventory_item_id = mic.inventory_item_id
AND oh.attribute1 = mic.organization_id
AND ol.line_category_code = 'ORDER'
AND mic.category_set_id = 1
AND oh.attribute1 = hr_sold.organization_id
AND loc.location_id = hr_sold.location_id
AND ol.ship_from_org_id = mp.organization_id
AND ol.attribute7 IS NOT NULL
AND (ol.attribute7 = acr.cash_receipt_id(+))
AND ol.flow_status_code IN(
'AWAITING_FULFILLMENT','AWAITING_SHIPPING',
'PICKED', 'BOOKED', 'PICKED PARTIAL'
)
AND flv.lookup_type = 'ITEM_TYPE'
AND flv.language = USERENV ('LANG')
AND flv.attribute1 = 'Merchandise'
AND flv.lookup_code = msib.item_type
AND osmv.lookup_code = ol.shipping_method_code
AND (osmv.meaning LIKE 'Customer-Pickup%' OR osmv.meaning LIKE 'Home Delivery%')
AND msib.inventory_item_id = sl.item_id
AND sl.vendor_id = pov.vendor_id
AND sl.attribute5 = 'Primary'
AND TRUNC (NVL (acr.receipt_date , oh.ordered_date)) <= TRUNC (SYSDATE)
--To display the records for which the receipt date is less than or equal to system date
AND (sl.disable_flag IS NULL OR sl.disable_flag = 'N')
--To display active vendor sites only
GROUP BY hr_sold.name,
mic.segment4,
msib.segment1,
msib.inventory_item_id,
oh.order_number,
srep.name,
ol.flow_status_code,
osmv.meaning,
mp.organization_code,
acr.receipt_date,
ol.schedule_ship_date,
cic.item_cost,
msib.list_price_per_unit,
cic.organization_id,
cic.inventory_item_id,
ol.ordered_quantity,
ol.unit_list_price,
ol.unit_selling_price,
DECODE (SUBSTR (loc.tax_name, 1, 3),
'Bay', 'Bay',
'Zel', 'Zellers',
'KMT', 'Kmart'
),
mic.segment1,
mic.segment2,
mic.segment3,
msib.description,
pov.segment1,
pov.vendor_name
ORDER BY 1, 2, 5, 3, 4;
BEGIN
l_err_location := 'Error in reading profile variable';
/* l_top contains the outbound path */
fnd_profile.get ('XXCOFIDATA_TOP', l_top);
IF l_top IS NULL OR l_top = ''
THEN
l_top := '/cdv01/oracle/cdvappl/xxcofidata/1.0.0/outbound';
ELSE
l_top := l_top || '/outbound';
END IF;
/********************************************************/
/* Open/create Output to build report in CSV format. */
/********************************************************/
l_err_location := 'Error in open output file';
l_file_hd1 :=
UTL_FILE.fopen (l_top,
l_report_name
|| '_'
|| TO_CHAR (SYSDATE, 'YYYYMMDDHH24MI')
|| '.csv',
'w'
);
/* Put start message in the concurrent manager log file. */
/************************************************************/
write_log (' ');
write_log ('START RUNNING ' || l_report_name || ' REPORT PROGRAM.');
write_log ('-***-');
write_log (' ');
write_log ('Date used in generating this report: ' || SYSDATE);
write_log (' ');
/********************************************************/
/* Begin to generate CSV files */
/********************************************************/
write_log ('Outputing ' || l_report_name || '*.csv FILE TO ' || l_top);
write_log ('START GENERATING REPORT...');
/* Report Column Header Details */
l_tempstr :=
'COFI Merchandise Sold Not Delivered Split Report,,,,,,,,,,,,,'
|| SYSDATE;
write_detail_record (l_tempstr);
l_tempstr := 'For Month End:,,' || TO_CHAR (SYSDATE, 'DD-MON-YYYY');
write_detail_record (l_tempstr);
write_detail_record ( 'In_Stock'
|| ','
|| 'On_Order'
|| ','
|| 'Banner'
|| ','
|| 'Store'
|| ','
|| 'Category'
|| ','
|| 'GMA'
|| ','
|| 'Group_Name'
|| ','
|| 'DEPT'
|| ','
|| 'Item'
|| ','
|| 'Item_Description'
|| ','
|| 'Vendor_Number'
|| ','
|| 'Vendor_Name'
|| ','
|| 'Sales_Order_No'
|| ','
|| 'Sales_Associate'
|| ','
|| 'Status'
|| ','
|| 'Shipping_Method'
|| ','
|| 'Fulfillment_Location'
|| ','
|| 'POS_Processing_Date'
|| ','
|| 'Promised_Date'
|| ','
|| 'Financial_Period'
|| ','
|| 'Quantity'
|| ','
|| 'Item_Cost'
|| ','
|| '"'
|| 'Total_Frozen_Cost'
|| '",'
|| '"'
|| 'Total_List_Price'
|| '",'
|| '"'
|| 'Total_Sales_Price'
|| '",'
|| '"'
|| 'Provisional_MD'
|| '"'
);
/* Make record count as zero */
l_record_no := 0;
/* Start writing record column details to UTL File */
l_err_location := 'Error in retrieving rows from database';
FOR out_rec IN cur_master
LOOP
l_err_location := 'Error in writing detail record';
write_detail_record ( out_rec.in_stock
|| ','
|| out_rec.on_order
|| ','
|| out_rec.banner
|| ','
|| out_rec.store
|| ','
|| out_rec.category
|| ','
|| out_rec.gma
|| ','
|| out_rec.group_name
|| ','
|| out_rec.dept
|| ','
|| out_rec.item
|| ','
|| out_rec.item_description
|| ','
|| out_rec.vendor_number
|| ','
|| out_rec.vendor_name
|| ','
|| out_rec.sales_order_no
|| ','
|| '"'
|| out_rec.sales_associate
|| '"'
|| ','
|| out_rec.status
|| ','
|| out_rec.shipping_method
|| ','
|| out_rec.fulfillment_location
|| ','
|| TO_CHAR (out_rec.pos_processing_date,
'DD-MON-YYYY'
)
|| ','
|| TO_CHAR (out_rec.promised_date,
'DD-MON-YYYY')
|| ','
|| out_rec.financial_period
|| ','
|| out_rec.quantity
|| ','
|| '"'
|| TO_CHAR (out_rec.item_cost,
'$999,999,999.90')
|| '",'
|| '"'
|| TO_CHAR (out_rec.total_frozen_cost,
'$999,999,999.90'
)
|| '",'
|| '"'
|| TO_CHAR (out_rec.total_list_price,
'$999,999,999.90'
)
|| '",'
|| '"'
|| TO_CHAR (out_rec.total_sales_price,
'$999,999,999.90'
)
|| '",'
|| '"'
|| TO_CHAR (out_rec.provisional_md,
'$999,999,999.90'
)
|| '"'
);
/* Increment the record count */
l_record_no := l_record_no + 1;
END LOOP;
IF l_record_no = 0
THEN
write_log (' ');
write_log ('There are no data available for building the report');
write_log (' ');
write_detail_record (' ');
write_detail_record
('There are no data available for building the report');
write_detail_record (' ');
UTL_FILE.fflush (l_file_hd1);
UTL_FILE.fclose (l_file_hd1);
errbuf := l_report_name || ' REPORT FILE JOB COMPLETED SUCCESSFULLY';
retcode := '0';
/*****************************************************/
/* Output Number Of Record in detail transactions. */
/*****************************************************/
write_log (' ');
write_log ('Total records for report: ' || l_record_no);
write_log (' ');
write_log ('JOB COMPLETED WITH NO DATA AVAILABLE');
fnd_file.CLOSE;
-- (FND_FILE.OUTPUT);
RETURN;
END IF;
UTL_FILE.fflush (l_file_hd1);
UTL_FILE.fclose (l_file_hd1);
errbuf := l_report_name || ' REPORT FILE JOB COMPLETED SUCCESSFULLY';
retcode := '0';
/*****************************************************/
/* Output Number Of Record in detail transactions. */
/*****************************************************/
write_log (' ');
write_log ('Total records for report: ' || l_record_no);
write_log (' ');
write_log ('JOB COMPLETED SUCCESSFULLY');
fnd_file.CLOSE; -- (FND_FILE.OUTPUT);
EXCEPTION
WHEN UTL_FILE.invalid_filehandle
THEN
UTL_FILE.fclose (l_file_hd1);
errbuf := 'INVALID FILE HANDLE';
retcode := '1';
write_error_record ( l_err_location
|| ': '
|| SQLCODE
|| ' AND ERROR is INVALID FILE HANDLE : '
|| SQLERRM
);
fnd_file.CLOSE;
WHEN UTL_FILE.invalid_path
THEN
UTL_FILE.fclose (l_file_hd1);
errbuf := 'INVALID PATH';
retcode := '2';
write_error_record ( l_err_location
|| ': '
|| SQLCODE
|| ' AND ERROR is INVALID PATH : '
|| SQLERRM
);
fnd_file.CLOSE;
WHEN UTL_FILE.invalid_mode
THEN
UTL_FILE.fclose (l_file_hd1);
errbuf := 'INVALID MODE';
retcode := '3';
write_error_record ( l_err_location
|| ': '
|| SQLCODE
|| ' AND ERROR is INVALID MODE : '
|| SQLERRM
);
fnd_file.CLOSE;
WHEN UTL_FILE.invalid_operation
THEN
UTL_FILE.fclose (l_file_hd1);
errbuf := 'INVALID OPERATION';
retcode := '4';
write_error_record ( l_err_location
|| ': '
|| SQLCODE
|| ' AND ERROR is INVALID OPERATION : '
|| SQLERRM
);
fnd_file.CLOSE;
WHEN UTL_FILE.read_error
THEN
UTL_FILE.fclose (l_file_hd1);
errbuf := 'READ ERROR';
retcode := '5';
write_error_record ( l_err_location
|| ': '
|| SQLCODE
|| ' AND ERROR is READ ERROR : '
|| SQLERRM
);
fnd_file.CLOSE;
WHEN UTL_FILE.write_error
THEN
UTL_FILE.fclose (l_file_hd1);
errbuf := 'WRITE ERROR';
retcode := '6';
write_error_record ( l_err_location
|| ': '
|| SQLCODE
|| ' AND ERROR is WRITE ERROR : '
|| SQLERRM
);
fnd_file.CLOSE;
WHEN UTL_FILE.internal_error
THEN
UTL_FILE.fclose (l_file_hd1);
errbuf := 'INTERNAL ERROR';
retcode := '7';
write_error_record ( l_err_location
|| ': '
|| SQLCODE
|| ' AND ERROR is INTERNAL ERROR : '
|| SQLERRM
);
fnd_file.CLOSE;
WHEN NO_DATA_FOUND
THEN
UTL_FILE.fclose (l_file_hd1);
errbuf := 'NO DATA FOUND';
retcode := '8';
write_error_record ( l_err_location
|| ': '
|| SQLCODE
|| ' Error is NO DATA FOUND :'
|| SQLERRM
);
fnd_file.CLOSE;
WHEN OTHERS
THEN
UTL_FILE.fclose (l_file_hd1);
errbuf := 'OTHERS ';
retcode := '9';
write_error_record (l_err_location || ': ' || SQLERRM);
fnd_file.CLOSE;
END;
END xxcofi_mer_sold_not_del_pkg;
--Package xxcofi_mer_sold_not_del_pkg End
/
SHOW ERRORS
--Display Errors Occured
/
EXIT
No comments:
Post a Comment