CREATE OR REPLACE PACKAGE apps.xxcofi_mer_sold_not_del_pkg
AS
/* ------------------------------------------------------------------------------------
Program Name XXCOFI_MER_SOLD_NOT_DELIVER_PKG
TYPE PLSQL Package
Input Parms run_date IN DATE
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 (status BOOLEAN);
PROCEDURE write_log (content IN VARCHAR2);
PROCEDURE write_error_record (errmsg IN VARCHAR2);
PROCEDURE write_detail_record (content IN VARCHAR2);
FUNCTION getonhandqty (a_org_id NUMBER,a_item_id 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_DELIVER_PKG
TYPE PLSQL Package
Input Parms run_date IN DATE
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 */
/* v_file_hd1 is a UTL File Record Type */
v_file_hdl UTL_FILE.file_type;
/* err_location is used for maintaining error log */
err_location VARCHAR2 (80);
/* standalone is used for maintaing boolean values based on standalone mode */
standalone BOOLEAN := FALSE;
/* Temporary varibale for writing Header Details */
tempstr VARCHAR2 (200);
/* This procedure is used to Set the standalone mode */
PROCEDURE setstandalonemode (status BOOLEAN)
IS
BEGIN
standalone := status;
END;
/*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 */
PROCEDURE write_detail_record (content IN VARCHAR2)
IS
BEGIN
IF UTL_FILE.is_open (v_file_hdl)
THEN
UTL_FILE.put_line (v_file_hdl, content);
END IF;
IF NOT standalone
THEN
fnd_file.put_line (fnd_file.output, content);
END IF;
END;
/*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. */
PROCEDURE write_log (content IN VARCHAR2)
IS
BEGIN
IF standalone
THEN
/* If the program is running without concurrent manager, */
/* send the log message to screen. */
DBMS_OUTPUT.put_line (content);
ELSE
fnd_file.put_line (fnd_file.LOG, content);
END IF;
END;
/* This procedure is used for displaying error messages into the fnd out file. */
PROCEDURE write_error_record (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 (errmsg);
write_detail_record (' '); /* Produce blank line. */
/* Write the same message into the log file. */
write_log (errmsg);
END;
/*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. */
FUNCTION getonhandqty (a_org_id NUMBER, a_item_id NUMBER)
RETURN NUMBER
IS
l_qty NUMBER;
BEGIN
BEGIN
SELECT SUM (moq.transaction_quantity)
INTO l_qty
FROM apps.mtl_onhand_quantities moq
WHERE moq.organization_id = a_org_id
AND moq.inventory_item_id = a_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;
/* Main Procedure */
PROCEDURE main (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
report_name VARCHAR2 (50) := 'XXCOFI_RPT097';
report_title VARCHAR2 (100) := 'COFI Merchandise Sold Not Delivered Split Report';
err_ret VARCHAR2 (255);
l_record_no NUMBER := 0;
l_top VARCHAR2 (200) := NULL;
cdate VARCHAR2 (20);
ctime VARCHAR2 (20);
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 out_record_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)
AND (sl.disable_flag IS NULL OR sl.disable_flag = 'N')
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
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. */
/********************************************************/
err_location := 'Error in open output file';
v_file_hdl :=
UTL_FILE.fopen (l_top,
report_name
'_'
TO_CHAR (SYSDATE, 'YYYYMMDDHH24MI')
'.csv',
'w'
);
/* Put start message in the concurrent manager log file. */
/************************************************************/
write_log (' ');
write_log ('START RUNNING '
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 '
report_name
'*.csv FILE TO '
l_top);
write_log ('START GENERATING REPORT...');
/* Report Column Header Details */
tempstr :=
'COFI Merchandise Sold Not Delivered Split Report,,,,,,,,,,,,,'
SYSDATE;
write_detail_record (tempstr);
tempstr := 'For Month End:,,'
TO_CHAR (SYSDATE, 'DD-MON-YYYY');
write_detail_record (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 */
err_location := 'Error in retrieving rows from database';
FOR out_rec IN out_record_master
LOOP
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 (v_file_hdl);
UTL_FILE.fclose (v_file_hdl);
errbuf := 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 (v_file_hdl);
UTL_FILE.fclose (v_file_hdl);
errbuf := 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 (v_file_hdl);
errbuf := 'INVALID FILE HANDLE';
retcode := '1';
write_error_record ( err_location
': '
SQLCODE
' AND ERROR is INVALID FILE HANDLE : '
SQLERRM
);
fnd_file.CLOSE;
WHEN UTL_FILE.invalid_path
THEN
UTL_FILE.fclose (v_file_hdl);
errbuf := 'INVALID PATH';
retcode := '2';
write_error_record ( err_location
': '
SQLCODE
' AND ERROR is INVALID PATH : '
SQLERRM
);
fnd_file.CLOSE;
WHEN UTL_FILE.invalid_mode
THEN
UTL_FILE.fclose (v_file_hdl);
errbuf := 'INVALID MODE';
retcode := '3';
write_error_record ( err_location
': '
SQLCODE
' AND ERROR is INVALID MODE : '
SQLERRM
);
fnd_file.CLOSE;
WHEN UTL_FILE.invalid_operation
THEN
UTL_FILE.fclose (v_file_hdl);
errbuf := 'INVALID OPERATION';
retcode := '4';
write_error_record ( err_location
': '
SQLCODE
' AND ERROR is INVALID OPERATION : '
SQLERRM
);
fnd_file.CLOSE;
WHEN UTL_FILE.read_error
THEN
UTL_FILE.fclose (v_file_hdl);
errbuf := 'READ ERROR';
retcode := '5';
write_error_record ( err_location
': '
SQLCODE
' AND ERROR is READ ERROR : '
SQLERRM
);
fnd_file.CLOSE;
WHEN UTL_FILE.write_error
THEN
UTL_FILE.fclose (v_file_hdl);
errbuf := 'WRITE ERROR';
retcode := '6';
write_error_record ( err_location
': '
SQLCODE
' AND ERROR is WRITE ERROR : '
SQLERRM
);
fnd_file.CLOSE;
WHEN UTL_FILE.internal_error
THEN
UTL_FILE.fclose (v_file_hdl);
errbuf := 'INTERNAL ERROR';
retcode := '7';
write_error_record ( err_location
': '
SQLCODE
' AND ERROR is INTERNAL ERROR : '
SQLERRM
);
fnd_file.CLOSE;
WHEN NO_DATA_FOUND
THEN
UTL_FILE.fclose (v_file_hdl);
errbuf := 'NO DATA FOUND';
retcode := '8';
write_error_record ( err_location
': '
SQLCODE
' Error is NO DATA FOUND :'
SQLERRM
);
fnd_file.CLOSE;
WHEN OTHERS
THEN
UTL_FILE.fclose (v_file_hdl);
errbuf := 'OTHERS ';
retcode := '9';
write_error_record (err_location
': '
SQLERRM);
fnd_file.CLOSE;
END;
END xxcofi_mer_sold_not_del_pkg;
/
No comments:
Post a Comment