CREATE OR REPLACE PACKAGE INVGRCVK_BG_1 IS
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2,
p_in_org_id IN NUMBER);
RETCODE OUT VARCHAR2,
p_in_org_id IN NUMBER);
PROCEDURE bg_po_receipt_sysauto(p_org_id IN NUMBER);
PROCEDURE bg_rtv_sysauto(p_org_id IN NUMBER);
PROCEDURE bg_mtl_sysauto(p_org_id IN NUMBER);
PROCEDURE bg_wip_sysauto(p_org_id IN NUMBER);
procedure bg_cut_generate_id;
FUNCTION bg_get_receipt_qty(p_in_po_header_id IN NUMBER,
p_in_po_line_number IN NUMBER,
p_in_shipment_no IN NUMBER,
p_in_waybill IN VARCHAR2) RETURN NUMBER;
p_in_po_line_number IN NUMBER,
p_in_shipment_no IN NUMBER,
p_in_waybill IN VARCHAR2) RETURN NUMBER;
FUNCTION bg_get_conv_rate(p_in_po_number IN VARCHAR2,
p_in_org_id IN NUMBER,
p_in_trx_date IN DATE) RETURN NUMBER;
FUNCTION bg_get_employee_name(p_in_user_id IN NUMBER) RETURN VARCHAR2;
END INVGRCVK_BG_1;
CREATE OR REPLACE PACKAGE BODY INVGRCVK_BG_1 IS
/*
* ID Date Modification Description
* ----------- ------------- -----------------------------------------------------------------
* LiuZerui 2011-05-10 Created
* Concurrent Program:
* Short Name : INVGRCVK_BG_X
* Executable : INVGRCVK_BG_1
* Request Group :
* Generate receipt number function : BG_GEN_RCV_NUM(215,1);
*/
* ID Date Modification Description
* ----------- ------------- -----------------------------------------------------------------
* LiuZerui 2011-05-10 Created
* Concurrent Program:
* Short Name : INVGRCVK_BG_X
* Executable : INVGRCVK_BG_1
* Request Group :
* Generate receipt number function : BG_GEN_RCV_NUM(215,1);
*/
v_org_id NUMBER;
V_USER_ID NUMBER;
l_req_id NUMBER;
l_complete_flag BOOLEAN;
l_phase VARCHAR2(100);
l_status VARCHAR2(100);
l_dev_phase VARCHAR2(100);
l_dev_status VARCHAR2(100);
l_message VARCHAR2(1000);
v_count NUMBER := 0;
V_USER_ID NUMBER;
l_req_id NUMBER;
l_complete_flag BOOLEAN;
l_phase VARCHAR2(100);
l_status VARCHAR2(100);
l_dev_phase VARCHAR2(100);
l_dev_status VARCHAR2(100);
l_message VARCHAR2(1000);
v_count NUMBER := 0;
v_receipt_number rcv_shipment_headers.receipt_num%TYPE;
v_rcv_h_interface_currval NUMBER;
v_shipment_header_id NUMBER;
v_interface_process_status RCV_HEADERS_INTERFACE.Processing_Status_Code%TYPE;
v_rcv_h_interface_currval NUMBER;
v_shipment_header_id NUMBER;
v_interface_process_status RCV_HEADERS_INTERFACE.Processing_Status_Code%TYPE;
/*v_shipment_num RCV_SHIPMENT_HEADERS.Shipment_Num%TYPE;
v_SHIPPED_DATE RCV_SHIPMENT_HEADERS.SHIPPED_DATE%TYPE :=sysdate-30;
v_packing_slip RCV_SHIPMENT_HEADERS.Packing_Slip%TYPE := 'ps';
V_Freight_Carrier RCV_SHIPMENT_HEADERS.Freight_Carrier_Code%TYPE := 'COMPANY TRUCK';
V_Bill_Lading RCV_SHIPMENT_HEADERS.Bill_Of_Lading%TYPE := 'Test01';
V_Container RCV_SHIPMENT_HEADERS.Num_Of_Containers%TYPE := 119;
v_comments RCV_SHIPMENT_HEADERS.Comments%TYPE := 'Testing';*/
v_SHIPPED_DATE RCV_SHIPMENT_HEADERS.SHIPPED_DATE%TYPE :=sysdate-30;
v_packing_slip RCV_SHIPMENT_HEADERS.Packing_Slip%TYPE := 'ps';
V_Freight_Carrier RCV_SHIPMENT_HEADERS.Freight_Carrier_Code%TYPE := 'COMPANY TRUCK';
V_Bill_Lading RCV_SHIPMENT_HEADERS.Bill_Of_Lading%TYPE := 'Test01';
V_Container RCV_SHIPMENT_HEADERS.Num_Of_Containers%TYPE := 119;
v_comments RCV_SHIPMENT_HEADERS.Comments%TYPE := 'Testing';*/
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2,
p_in_org_id IN NUMBER) IS
v_po_rece NUMBER;
v_rtv NUMBER;
v_mtl NUMBER;
v_wip NUMBER;
CURSOR c_num IS
SELECT (SELECT COUNT(c_transaction_id)
FROM BG_CUT_RCV_Interface
WHERE transaction_type = 'PO'
AND processing_status = 'P'
AND organization_id = p_in_org_id
AND ROWNUM < 2) po,
(SELECT COUNT(c_transaction_id)
FROM BG_CUT_RCV_Interface
WHERE transaction_type = 'RTV'
AND processing_status = 'P'
AND organization_id = p_in_org_id
AND ROWNUM < 2) rtv,
(SELECT COUNT(c_transaction_id)
FROM BG_CUT_MTL_Interface
WHERE transaction_type = 'MTL'
AND processing_status = 'P'
AND organization_id = p_in_org_id
AND ROWNUM < 2) mtl,
(SELECT COUNT(c_transaction_id)
FROM BG_CUT_WIP_Interface
WHERE transaction_type = 'WIP'
AND processing_status = 'P'
AND organization_id = p_in_org_id
AND ROWNUM < 2) wip
FROM dual;
BEGIN
---Generate CUT ID includes PO and JOB --
bg_cut_generate_id;
---Generate CUT ID includes PO and JOB --
v_org_id := p_in_org_id;
OPEN c_num;
FETCH c_num
INTO v_po_rece, v_rtv, v_mtl, v_wip;
CLOSE c_num;
--initialize
/* begin
fnd_global.apps_initialize(user_id=>3272,resp_id=>53528,resp_appl_id=>401);
end;*/
--po receipt
IF v_po_rece <> 0 THEN
INVGRCVK_BG_1.bg_po_receipt_sysauto(v_org_id);
END IF;
--return to vendor
IF v_rtv <> 0 THEN
INVGRCVK_BG_1.bg_rtv_sysauto(v_org_id);
END IF;
--subinventory transfor
IF v_mtl <> 0 THEN
invgrcvk_bg_1.bg_mtl_sysauto(v_org_id);
END IF;
--WIP completion
IF v_wip <> 0 THEN
invgrcvk_bg_1.bg_wip_sysauto(v_org_id);
END IF;
END MAIN;
RETCODE OUT VARCHAR2,
p_in_org_id IN NUMBER) IS
v_po_rece NUMBER;
v_rtv NUMBER;
v_mtl NUMBER;
v_wip NUMBER;
CURSOR c_num IS
SELECT (SELECT COUNT(c_transaction_id)
FROM BG_CUT_RCV_Interface
WHERE transaction_type = 'PO'
AND processing_status = 'P'
AND organization_id = p_in_org_id
AND ROWNUM < 2) po,
(SELECT COUNT(c_transaction_id)
FROM BG_CUT_RCV_Interface
WHERE transaction_type = 'RTV'
AND processing_status = 'P'
AND organization_id = p_in_org_id
AND ROWNUM < 2) rtv,
(SELECT COUNT(c_transaction_id)
FROM BG_CUT_MTL_Interface
WHERE transaction_type = 'MTL'
AND processing_status = 'P'
AND organization_id = p_in_org_id
AND ROWNUM < 2) mtl,
(SELECT COUNT(c_transaction_id)
FROM BG_CUT_WIP_Interface
WHERE transaction_type = 'WIP'
AND processing_status = 'P'
AND organization_id = p_in_org_id
AND ROWNUM < 2) wip
FROM dual;
BEGIN
---Generate CUT ID includes PO and JOB --
bg_cut_generate_id;
---Generate CUT ID includes PO and JOB --
v_org_id := p_in_org_id;
OPEN c_num;
FETCH c_num
INTO v_po_rece, v_rtv, v_mtl, v_wip;
CLOSE c_num;
--initialize
/* begin
fnd_global.apps_initialize(user_id=>3272,resp_id=>53528,resp_appl_id=>401);
end;*/
--po receipt
IF v_po_rece <> 0 THEN
INVGRCVK_BG_1.bg_po_receipt_sysauto(v_org_id);
END IF;
--return to vendor
IF v_rtv <> 0 THEN
INVGRCVK_BG_1.bg_rtv_sysauto(v_org_id);
END IF;
--subinventory transfor
IF v_mtl <> 0 THEN
invgrcvk_bg_1.bg_mtl_sysauto(v_org_id);
END IF;
--WIP completion
IF v_wip <> 0 THEN
invgrcvk_bg_1.bg_wip_sysauto(v_org_id);
END IF;
END MAIN;
---Used to generate CUT ID includes PO and JOB --
procedure bg_cut_generate_id is
cursor cur_rec_po is
--PO Part--
select poh.org_id,
poh.segment1 source_number,
poh.po_header_id source_id,
pol.po_line_id source_line_id,
msi.segment1 item_number,
pol.item_id item_id,
nvl(pll.quantity - pll.quantity_received, 0) QTY,
'PO' source_type,
nvl(pol.attribute4, null) CUTTER_TYPE
from po_headers_all poh,
po_lines_all pol,
apps.po_line_locations_all pll,
mtl_system_items_b msi
where poh.po_header_id = pol.po_header_id
AND msi.inventory_item_id = pol.item_id
AND msi.organization_id = poh.org_id
---
AND pll.po_header_id = poh.po_header_id
AND pll.po_line_id = pol.po_line_id
AND nvl(pll.cancel_flag, 'N') <> 'Y'
AND pll.org_id = poh.org_id
AND pll.Closed_Code = 'OPEN'
AND nvl(pll.quantity - pll.quantity_received, 0) > 0
--and poh.ATTRIBUTE9 = 'G'
AND msi.segment1 LIKE 'C%'
-- and poh.segment1 = '20120402704'
and poh.org_id = 215
AND poh.Authorization_Status = 'APPROVED'
AND poh.type_lookup_code = 'STANDARD'
AND nvl(pol.cancel_flag, 'N') <> 'Y'
-- AND poh.creation_date be
and nvl(pol.ATTRIBUTE3, 'N') = 'N' --Update Remark--
and pol.attribute4 is not null --cutter type
group by poh.org_id,
poh.segment1,
poh.po_header_id,
pol.po_line_id,
msi.segment1,
pol.item_id,
nvl(pll.quantity - pll.quantity_received, 0),
nvl(pol.attribute4, null)
order by 8, 2;
--Job Part--
cursor cur_rec_job is
SELECT msi.organization_id org_id,
we.wip_entity_name source_number,
we.wip_entity_id source_id,
null source_line_id,
msi.segment1 item_number,
msi.inventory_item_id item_id,
decode(WDJ.START_QUANTITY - WDJ.QUANTITY_COMPLETED -
WDJ.QUANTITY_SCRAPPED,
0,
0,
WDJ.START_QUANTITY - WDJ.QUANTITY_COMPLETED -
WDJ.QUANTITY_SCRAPPED) QTY,
'JOB' source_type,
nvl(wdj.ATTRIBUTE13, null) CUTTER_TYPE
FROM wip_entities we, wip_discrete_jobs wdj, mtl_system_items_b msi
WHERE we.wip_entity_id = wdj.wip_entity_id
AND msi.inventory_item_id = wdj.primary_item_id
AND msi.organization_id = wdj.organization_id
AND wdj.status_type = 3
--AND we.wip_entity_name = 'CUT1203047'
AND msi.segment1 LIKE 'C%'
AND msi.organization_id = 215
AND nvl(wdj.ATTRIBUTE12, 'N') = 'N' --Update Remark--
AND wdj.ATTRIBUTE13 is not null --cutter type
order by 8, 2;
V_DB_SEQUENCE_NAME varchar2(50);
v_init_value number;
v_year varchar2(10) := null;
v_week varchar2(10) := null;
v_ddl_sql varchar2(1000);
X number;
Y number;
begin
--
select to_char(sysdate, 'YY') into v_year from dual;
select to_char(sysdate, 'FMWW') into v_week from dual;
--
V_DB_SEQUENCE_NAME := 'BG_CUT_SEQ_' || to_number(v_year || v_week) || '_S';
dbms_output.put_line(V_DB_SEQUENCE_NAME);
begin
select count(*)
into X
from dba_sequences F
where f.sequence_name = V_DB_SEQUENCE_NAME;
exception
when others then
X := 0;
end;
if X = 0 then
dbms_output.put_line('Create Sequence' || V_DB_SEQUENCE_NAME);
v_init_value := v_year || v_week || '00001';
v_ddl_sql := 'CREATE SEQUENCE ' || V_DB_SEQUENCE_NAME ||
' MINVALUE 1 NOMAXVALUE START WITH ' || v_init_value ||
' NOCACHE ORDER NOCYCLE';
execute immediate v_ddl_sql;
/*FND_SEQNUM.create_db_seq(V_DB_SEQUENCE_NAME,v_init_value);*/
end if;
V_DB_SEQUENCE_NAME := V_DB_SEQUENCE_NAME || '.nextval';
dbms_output.put_line(V_DB_SEQUENCE_NAME);
--Part 1.PO--
FOR rec_po IN cur_rec_po LOOP
--Insert Cut ID --
for i in 1 .. rec_po.QTY loop
execute immediate 'select ' || V_DB_SEQUENCE_NAME || ' from dual'
into Y;
insert into BG_CUT_CUTTER_ID
(org_id,
source_id,
source_line_id,
source_type,
item_id,
cutter_id,
CUTTER_TYPE )
values
(rec_po.org_id,
rec_po.source_id,
rec_po.source_line_id,
rec_po.source_type,
rec_po.item_id,
Y,
rec_po.CUTTER_TYPE
);
end loop;
--
---Update Remake column for PO line --
update po_lines_all P
set P.ATTRIBUTE3 = 'Y'
where P.Po_Line_Id = rec_po.source_line_id;
end loop;
--Part 2.JOb--
FOR rec_cut IN cur_rec_job LOOP
--Insert Cut ID --
for i in 1 .. rec_cut.QTY loop
execute immediate 'select ' || V_DB_SEQUENCE_NAME || ' from dual'
into Y;
insert into BG_CUT_CUTTER_ID
(org_id,
source_id,
source_line_id,
source_type,
item_id,
cutter_id,
CUTTER_TYPE )
values
(rec_cut.org_id,
rec_cut.source_id,
rec_cut.source_line_id,
rec_cut.source_type,
rec_cut.item_id,
Y,
rec_cut.CUTTER_TYPE );
end loop;
--
update wip_discrete_jobs j
set j.attribute12 = 'Y'
where j.wip_entity_id = rec_cut.source_id;
---Update Remake column for PO line and JoB header--
end loop;
COMMIT;
end bg_cut_generate_id;
procedure bg_cut_generate_id is
cursor cur_rec_po is
--PO Part--
select poh.org_id,
poh.segment1 source_number,
poh.po_header_id source_id,
pol.po_line_id source_line_id,
msi.segment1 item_number,
pol.item_id item_id,
nvl(pll.quantity - pll.quantity_received, 0) QTY,
'PO' source_type,
nvl(pol.attribute4, null) CUTTER_TYPE
from po_headers_all poh,
po_lines_all pol,
apps.po_line_locations_all pll,
mtl_system_items_b msi
where poh.po_header_id = pol.po_header_id
AND msi.inventory_item_id = pol.item_id
AND msi.organization_id = poh.org_id
---
AND pll.po_header_id = poh.po_header_id
AND pll.po_line_id = pol.po_line_id
AND nvl(pll.cancel_flag, 'N') <> 'Y'
AND pll.org_id = poh.org_id
AND pll.Closed_Code = 'OPEN'
AND nvl(pll.quantity - pll.quantity_received, 0) > 0
--and poh.ATTRIBUTE9 = 'G'
AND msi.segment1 LIKE 'C%'
-- and poh.segment1 = '20120402704'
and poh.org_id = 215
AND poh.Authorization_Status = 'APPROVED'
AND poh.type_lookup_code = 'STANDARD'
AND nvl(pol.cancel_flag, 'N') <> 'Y'
-- AND poh.creation_date be
and nvl(pol.ATTRIBUTE3, 'N') = 'N' --Update Remark--
and pol.attribute4 is not null --cutter type
group by poh.org_id,
poh.segment1,
poh.po_header_id,
pol.po_line_id,
msi.segment1,
pol.item_id,
nvl(pll.quantity - pll.quantity_received, 0),
nvl(pol.attribute4, null)
order by 8, 2;
--Job Part--
cursor cur_rec_job is
SELECT msi.organization_id org_id,
we.wip_entity_name source_number,
we.wip_entity_id source_id,
null source_line_id,
msi.segment1 item_number,
msi.inventory_item_id item_id,
decode(WDJ.START_QUANTITY - WDJ.QUANTITY_COMPLETED -
WDJ.QUANTITY_SCRAPPED,
0,
0,
WDJ.START_QUANTITY - WDJ.QUANTITY_COMPLETED -
WDJ.QUANTITY_SCRAPPED) QTY,
'JOB' source_type,
nvl(wdj.ATTRIBUTE13, null) CUTTER_TYPE
FROM wip_entities we, wip_discrete_jobs wdj, mtl_system_items_b msi
WHERE we.wip_entity_id = wdj.wip_entity_id
AND msi.inventory_item_id = wdj.primary_item_id
AND msi.organization_id = wdj.organization_id
AND wdj.status_type = 3
--AND we.wip_entity_name = 'CUT1203047'
AND msi.segment1 LIKE 'C%'
AND msi.organization_id = 215
AND nvl(wdj.ATTRIBUTE12, 'N') = 'N' --Update Remark--
AND wdj.ATTRIBUTE13 is not null --cutter type
order by 8, 2;
V_DB_SEQUENCE_NAME varchar2(50);
v_init_value number;
v_year varchar2(10) := null;
v_week varchar2(10) := null;
v_ddl_sql varchar2(1000);
X number;
Y number;
begin
--
select to_char(sysdate, 'YY') into v_year from dual;
select to_char(sysdate, 'FMWW') into v_week from dual;
--
V_DB_SEQUENCE_NAME := 'BG_CUT_SEQ_' || to_number(v_year || v_week) || '_S';
dbms_output.put_line(V_DB_SEQUENCE_NAME);
begin
select count(*)
into X
from dba_sequences F
where f.sequence_name = V_DB_SEQUENCE_NAME;
exception
when others then
X := 0;
end;
if X = 0 then
dbms_output.put_line('Create Sequence' || V_DB_SEQUENCE_NAME);
v_init_value := v_year || v_week || '00001';
v_ddl_sql := 'CREATE SEQUENCE ' || V_DB_SEQUENCE_NAME ||
' MINVALUE 1 NOMAXVALUE START WITH ' || v_init_value ||
' NOCACHE ORDER NOCYCLE';
execute immediate v_ddl_sql;
/*FND_SEQNUM.create_db_seq(V_DB_SEQUENCE_NAME,v_init_value);*/
end if;
V_DB_SEQUENCE_NAME := V_DB_SEQUENCE_NAME || '.nextval';
dbms_output.put_line(V_DB_SEQUENCE_NAME);
--Part 1.PO--
FOR rec_po IN cur_rec_po LOOP
--Insert Cut ID --
for i in 1 .. rec_po.QTY loop
execute immediate 'select ' || V_DB_SEQUENCE_NAME || ' from dual'
into Y;
insert into BG_CUT_CUTTER_ID
(org_id,
source_id,
source_line_id,
source_type,
item_id,
cutter_id,
CUTTER_TYPE )
values
(rec_po.org_id,
rec_po.source_id,
rec_po.source_line_id,
rec_po.source_type,
rec_po.item_id,
Y,
rec_po.CUTTER_TYPE
);
end loop;
--
---Update Remake column for PO line --
update po_lines_all P
set P.ATTRIBUTE3 = 'Y'
where P.Po_Line_Id = rec_po.source_line_id;
end loop;
--Part 2.JOb--
FOR rec_cut IN cur_rec_job LOOP
--Insert Cut ID --
for i in 1 .. rec_cut.QTY loop
execute immediate 'select ' || V_DB_SEQUENCE_NAME || ' from dual'
into Y;
insert into BG_CUT_CUTTER_ID
(org_id,
source_id,
source_line_id,
source_type,
item_id,
cutter_id,
CUTTER_TYPE )
values
(rec_cut.org_id,
rec_cut.source_id,
rec_cut.source_line_id,
rec_cut.source_type,
rec_cut.item_id,
Y,
rec_cut.CUTTER_TYPE );
end loop;
--
update wip_discrete_jobs j
set j.attribute12 = 'Y'
where j.wip_entity_id = rec_cut.source_id;
---Update Remake column for PO line and JoB header--
end loop;
COMMIT;
end bg_cut_generate_id;
PROCEDURE bg_po_receipt_sysauto(p_org_id IN NUMBER) IS
v_inv_period NUMBER;
v_pur_period NUMBER;
v_ap_period NUMBER;
v_ar_period NUMBER;
v_gl_period NUMBER;
v_conversion_rate gl_daily_rates.conversion_rate%TYPE;
v_curr_code po_headers_all.currency_code%TYPE;
v_operator VARCHAR2(100);
/*CURSOR c_curr_code(p_in_po_number IN VARCHAR2, p_in_org_id IN NUMBER) IS
SELECT poh.currency_code
FROM po_headers_all poh
WHERE poh.org_id = p_in_org_id
AND poh.segment1 = p_in_po_number;*/
CURSOR c_period_status(p_trx_date DATE) IS
SELECT (SELECT COUNT(p.status)
FROM ORG_ACCT_PERIODS_V p
WHERE p.organization_id = p_org_id
AND upper(p.status) = upper('Open')
AND trunc(p_trx_date) BETWEEN p.start_date AND p.end_date) inv_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 201
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) pur_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 200
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ap_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 222
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ar_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 101
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) gl_period
FROM dual;
CURSOR update_r(p_in_header_id VARCHAR2) IS
SELECT receipt_header_id, processing_status_code
FROM rcv_headers_interface
WHERE header_interface_id = p_in_header_id;
CURSOR receipt(p_req_id number) IS
select r.receipt_num
from rcv_shipment_headers r
where r.request_id = p_req_id;
CURSOR c_rcv_header IS
/* SELECT c_transaction_id,PO_Number,Organization_ID,WayBill ,Operator,
TRANSACTION_DATE*/
SELECT DISTINCT PO_Number,
Organization_ID,
GRN_Number,
WayBill,
Operator,
transaction_date
--to_char(transaction_date,'MON-YYYY') trx_period -- added in 2011/07/06
FROM BG_CUT_RCV_Interface bcr
WHERE PROCESSING_STATUS = 'P'
AND transaction_type = 'PO'
AND organization_id = p_org_id
ORDER BY transaction_date;
CURSOR c_rcv_po(p_in_po_number IN VARCHAR2, p_in_org_id IN NUMBER) IS
SELECT PO_HEADER_ID, VENDOR_ID, SEGMENT1, ORG_ID, vendor_site_id
FROM PO_HEADERS_ALL
WHERE segment1 = p_in_po_number
AND org_id = p_org_id;
CURSOR c_rcv_cms_oracle(p_po_number IN VARCHAR2,
p_in_waybill IN VARCHAR2,
p_in_trx_date IN DATE) IS
SELECT bcr.c_transaction_id,
bcr.po_number,
bcr.organization_id,
bcr.item_number,
bcr.line_number,
bcr.shipment_line_num,
bcr.grn_number,
bcr.subinventory_code,
bcr.mrb_no,
bcr.rma_number,
bcr.transaction_qty,
bcr.transaction_uom,
bcr.waybill,
bcr.operator /*,
bcr.Last_Update_Date,
bcr.Last_Updated_By,
bcr.creation_date,
bcr.created_by*/
FROM BG_CUT_RCV_Interface bcr
WHERE organization_id = p_org_id
AND bcr.waybill = p_in_waybill
AND bcr.processing_status = 'P'
AND trunc(bcr.transaction_date) = trunc(p_in_trx_date)
AND bcr.transaction_type = 'PO'
AND bcr.po_number = p_po_number
--AND bcr.c_transaction_id = p_transaction_id
ORDER BY bcr.line_number, bcr.shipment_line_num;
CURSOR c_rcv(p_po_header_id IN NUMBER,
p_po_line_number IN NUMBER,
p_shipment_number IN NUMBER,
p_waybill IN VARCHAR2) IS
SELECT pl.org_Id,
PL.ITEM_ID,
pl.po_header_id,
PL.PO_LINE_ID,
PL.LINE_NUM,
PLL.QUANTITY,
PL.UNIT_MEAS_LOOKUP_CODE,
MP.ORGANIZATION_CODE,
PLL.LINE_LOCATION_ID,
PLL.CLOSED_CODE,
PLL.QUANTITY_RECEIVED,
apps.invgrcvk_bg_1.bg_get_receipt_qty(p_po_header_id,
p_po_line_number,
p_shipment_number,
p_waybill) received_qty,
PLL.CANCEL_FLAG,
PLL.SHIPMENT_NUM,
pda.destination_type_code,
pda.deliver_to_person_id,
pll.ship_to_location_id,
pda.destination_subinventory,
pda.destination_organization_id
FROM PO_LINES_ALL PL,
PO_LINE_LOCATIONS_ALL PLL,
MTL_PARAMETERS MP,
apps.po_distributions_all pda
WHERE PL.PO_LINE_ID = PLL.PO_LINE_ID
AND PLL.SHIP_TO_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND pll.line_location_id = pda.line_location_id
AND pll.shipment_num = p_shipment_number
AND pl.line_num = p_po_line_number
AND Pl.PO_HEADER_ID = p_po_header_id; --291502 --X_PO_HEADER_ID
BEGIN
fnd_file.put_line(fnd_file.output,
'***CMS Data to Oracle RCV API Insert Script***');
dbms_output.put_line('***CMS Data to Oracle RCV API Insert Script***');
SELECT fnd_profile.value('USER_ID') INTO v_user_id FROM dual;
v_operator := INVGRCVK_BG_1.bg_get_employee_name(v_user_id);
FOR rec_rcv IN c_rcv_header LOOP
v_receipt_number := '';
v_rcv_h_interface_currval := '';
v_shipment_header_id := '';
v_interface_process_status := '';
OPEN c_period_status(rec_rcv.transaction_date);
FETCH c_period_status
INTO v_inv_period,
v_pur_period,
v_ap_period,
v_ar_period,
v_gl_period;
CLOSE c_period_status;
v_conversion_rate := INVGRCVK_BG_1.bg_get_conv_rate(rec_rcv.PO_Number,
p_org_id,
rec_rcv.TRANSACTION_date);
/* dbms_output.put_line('*00'||v_inv_period);
dbms_output.put_line('*00'||v_pur_period);
dbms_output.put_line('*00'||v_ap_period);
dbms_output.put_line('*00'||v_ar_period);
dbms_output.put_line('*00'||v_gl_period);
dbms_output.put_line('*00'||v_conversion_rate);*/
IF v_inv_period = 1 AND v_pur_period = 1 AND v_ap_period = 1 AND
v_ar_period = 1 AND v_gl_period = 1 AND
v_conversion_rate IS NOT NULL THEN
dbms_output.put_line('*01');
FOR rec_po IN c_rcv_po(rec_rcv.PO_Number, rec_rcv.organization_id) LOOP
INSERT INTO rcv_headers_interface
(header_interface_id,
GROUP_ID,
processing_status_code,
receipt_source_code,
transaction_type,
auto_transact_code,
last_update_date,
last_updated_by,
last_update_login,
creation_date, --receipt date--
created_by,
vendor_id,
vendor_site_id,
expected_receipt_date,
validation_flag,
--SHIPMENT_NUM,
--SHIPPED_DATE,
--PACKING_SLIP,
WAYBILL_AIRBILL_NUM,
--FREIGHT_CARRIER_CODE,
--BILL_OF_LADING,
--NUM_OF_CONTAINERS,
EMPLOYEE_NAME,
ATTRIBUTE_CATEGORY,
attribute1
--COMMENTS
)
VALUES
(rcv_headers_interface_s.NEXTVAL,
rcv_interface_groups_s.NEXTVAL,
'PENDING',
'VENDOR',
'NEW',
'RECEIVE',
SYSDATE,
V_USER_ID,
0,
rec_rcv.TRANSACTION_DATE,
V_USER_ID,
rec_po.vendor_id,
rec_po.vendor_site_id,
SYSDATE,
'Y',
--v_shipment_num, --'123',
--v_SHIPPED_DATE, --to_date('2010/10/01','yyyy/mm/dd'),
--v_packing_slip,--'a',
rec_rcv.WayBill,
--V_Freight_Carrier,--'COMPANY TRUCK',
--V_Bill_Lading,--'Test01',
--V_Container,--1,
v_operator, --rec_rcv.Operator,
p_org_id,
1
--v_comments--'COMMENTS'
);
FOR rec_t IN c_rcv_cms_oracle(rec_rcv.PO_Number,
rec_rcv.waybill,
rec_rcv.transaction_date) LOOP
dbms_output.put_line('*02');
FOR rec IN c_rcv(rec_po.po_header_id,
rec_t.line_number,
rec_t.shipment_line_num,
rec_t.waybill) LOOP
IF rec.closed_code IN ('APPROVED', 'OPEN') AND
rec.QUANTITY_RECEIVED < rec.QUANTITY AND
NVL(rec.CANCEL_FLAG, 'N') = 'N' THEN
INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
po_header_id,
PO_LINE_ID,
ITEM_ID,
QUANTITY,
UNIT_OF_MEASURE,
PO_LINE_LOCATION_ID,
AUTO_TRANSACT_CODE,
RECEIPT_SOURCE_CODE,
TO_ORGANIZATION_CODE,
to_organization_id,
ship_to_location_id,
SOURCE_DOCUMENT_CODE,
destination_type_code,
deliver_to_person_id,
deliver_to_location_id,
subinventory,
DOCUMENT_NUM,
HEADER_INTERFACE_ID,
VALIDATION_FLAG)
VALUES
(RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
RCV_INTERFACE_GROUPS_S.CURRVAL,
SYSDATE,
V_USER_ID,
SYSDATE,
V_USER_ID,
0,
'RECEIVE', --TRANSACTION_TYPE
rec_rcv.TRANSACTION_DATE, --SYSDATE ,
'PENDING',
'BATCH',
'PENDING',
rec.po_header_id,
rec.PO_LINE_ID,
rec.ITEM_ID,
--rec_t.transaction_qty ,
rec.received_qty,
rec_t.transaction_uom, --UNIT_OF_MEASURE
rec.LINE_LOCATION_ID,
'DELIVER',
'VENDOR',
rec.ORGANIZATION_CODE,
rec.org_id,
rec.ship_to_location_id,
'PO',
rec.destination_type_code,
rec.deliver_to_person_id,
rec.ship_to_location_id,
nvl(rec.destination_subinventory,
rec_t.subinventory_code),
rec_rcv.PO_Number,
RCV_HEADERS_INTERFACE_S.CURRVAL,
'Y');
DBMS_OUTPUT.PUT_LINE('PO line: ' || rec.LINE_NUM ||
' Shipment: ' || rec.SHIPMENT_NUM ||
' has been inserted into ROI.');
ELSE
DBMS_OUTPUT.PUT_LINE('PO line ' || rec.LINE_NUM ||
' is either closed, cancelled, received.');
END IF;
END LOOP;
--DBMS_OUTPUT.PUT_LINE('*** ezROI COMPLETE - End ***');
END LOOP;
END LOOP;
fnd_file.put_line(fnd_file.output,
'*** Call Receiving Transaction Processor Reoport ***');
/* fnd_global.apps_initialize(user_id => fnd_profile.value('USER_ID'),
resp_id => fnd_profile.value('RESP_ID'),
resp_appl_id => fnd_profile.value('RESP_APPL_ID'), --GL
security_group_id => 0);*/
l_req_id := fnd_request.submit_request('PO',
'RVCTP',
null,
sysdate,
false,
'BATCH',
null);
dbms_output.put_line('l_req_id: ' || l_req_id);
COMMIT;
IF l_req_id <= 0 THEN
fnd_file.put_line(fnd_file.output,
'*** Receiving Transaction Processor runing exception ***');
ELSIF l_req_id > 0 THEN
fnd_file.put_line(fnd_file.output,
'*** Receiving Transaction Processor Request Submit successful ***');
l_complete_flag := fnd_concurrent.wait_for_request(l_req_id,
1,
3600,
l_phase,
l_status,
l_dev_phase,
l_dev_status,
l_message);
-- use the Customized receipt number
SELECT rcv_headers_interface_s.CURRVAL
INTO v_rcv_h_interface_currval
FROM dual;
fnd_file.put_line(fnd_file.output,
'rcv_headers_interface_s: ' ||
v_rcv_h_interface_currval);
OPEN update_r(v_rcv_h_interface_currval);
FETCH update_r
INTO v_shipment_header_id, v_interface_process_status;
CLOSE update_r;
fnd_file.put_line(fnd_file.output,
'v_shipment_header_id: ' ||
v_shipment_header_id);
fnd_file.put_line(fnd_file.output,
'rec_rcv.grn_number: ' || rec_rcv.grn_number);
IF rec_rcv.grn_number IS NULL AND
v_shipment_header_id IS NOT NULL THEN
SELECT BG_GEN_RCV_NUM(p_org_id, 1)
INTO v_receipt_number
FROM dual;
ELSE
v_receipt_number := rec_rcv.grn_number;
END IF;
IF v_interface_process_status = 'SUCCESS' THEN
UPDATE rcv_shipment_headers
SET receipt_num = v_receipt_number --rec_rcv.grn_number
WHERE shipment_header_id = v_shipment_header_id;
UPDATE BG_CUT_RCV_Interface
SET PROCESSING_STATUS = 'S', grn_number = v_receipt_number
WHERE PROCESSING_STATUS = 'P'
AND trunc(TRANSACTION_DATE) =
trunc(rec_rcv.transaction_date)
AND transaction_type = 'PO'
AND waybill = rec_rcv.waybill
AND po_number = rec_rcv.po_number;
--AND c_transaction_id = rec_rcv.c_transaction_id;
END IF;
COMMIT;
END IF;
IF l_complete_flag THEN
FOR CURSOR3 IN receipt(l_req_id) LOOP
IF CURSOR3.receipt_num IS NULL THEN
--** check the po_interface_errors*--
select count(*)
into v_count
from po_interface_errors a
where a.request_id = l_req_id;
fnd_file.put_line(fnd_file.output,
'The po_interface_errors exists error , you can runing the report "Receiving Interface Errors Report" to check !' ||
CURSOR3.receipt_num);
DBMS_OUTPUT.PUT_LINE('The po_interface_errors exists error , you can runing the report "Receiving Interface Errors Report" to check !' ||
CURSOR3.receipt_num);
--** check the po_interface_errors*--
ELSE
fnd_file.put_line(fnd_file.output,
'Receipt Number:' || CURSOR3.receipt_num);
DBMS_OUTPUT.PUT_LINE('Receipt Number:' ||
CURSOR3.receipt_num);
END IF;
END LOOP;
ELSE
fnd_file.put_line(fnd_file.output,
'*** Receiving Transaction Processing ***');
DBMS_OUTPUT.PUT_LINE('*** Receiving Transaction Processing ***');
END IF;
ELSIF v_conversion_rate IS NULL THEN
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'The PO number: ' || rec_rcv.po_number ||
' have no conversion rate in ' ||
to_char(rec_rcv.transaction_date, 'yyyy/mm/dd'));
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
ELSE
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'*** The period is not opened! *** Period: ' ||
to_char(rec_rcv.transaction_date, 'MON-YYYY'));
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
END IF;
v_conversion_rate := NULL;
END LOOP;
COMMIT;
/*
fnd_file.put_line(fnd_file.output, '*** Call Receiving Transaction Processor Reoport ***');
fnd_global.apps_initialize(user_id => fnd_profile.value('USER_ID'),
resp_id => fnd_profile.value('RESP_ID'),
resp_appl_id => fnd_profile.value('RESP_APPL_ID'), --GL
security_group_id => 0);
l_req_id := fnd_request.submit_request('PO',
'RVCTP',
null,
sysdate,
false,
'BATCH',
null);
dbms_output.put_line('l_req_id: '||l_req_id);
COMMIT;
IF l_req_id <= 0 THEN
fnd_file.put_line(fnd_file.output,'*** Receiving Transaction Processor runing exception ***');
ELSIF l_req_id > 0 THEN
fnd_file.put_line(fnd_file.output,'*** Receiving Transaction Processor Request Submit successful ***');
COMMIT;
l_complete_flag := fnd_concurrent.wait_for_request(l_req_id,
1,
3600,
l_phase,
l_status,
l_dev_phase,
l_dev_status,
l_message);
END IF;
IF l_complete_flag THEN
FOR CURSOR3 IN receipt(l_req_id) LOOP
IF CURSOR3.receipt_num IS NULL THEN
--** check the po_interface_errors*--
select count(*)into v_count
from po_interface_errors a where a.request_id=l_req_id;
fnd_file.put_line(fnd_file.output,'The po_interface_errors exists error , you can runing the report "Receiving Interface Errors Report" to check !' || CURSOR3.receipt_num);
DBMS_OUTPUT.PUT_LINE('The po_interface_errors exists error , you can runing the report "Receiving Interface Errors Report" to check !' || CURSOR3.receipt_num);
--** check the po_interface_errors*--
ELSE
fnd_file.put_line(fnd_file.output,'Receipt Number:' || CURSOR3.receipt_num);
DBMS_OUTPUT.PUT_LINE('Receipt Number:' || CURSOR3.receipt_num);
END IF;
END LOOP;
ELSE
fnd_file.put_line(fnd_file.output,'*** Receiving Transaction Processing ***');
DBMS_OUTPUT.PUT_LINE('*** Receiving Transaction Processing ***');
END IF;*/
END bg_po_receipt_sysauto;
v_inv_period NUMBER;
v_pur_period NUMBER;
v_ap_period NUMBER;
v_ar_period NUMBER;
v_gl_period NUMBER;
v_conversion_rate gl_daily_rates.conversion_rate%TYPE;
v_curr_code po_headers_all.currency_code%TYPE;
v_operator VARCHAR2(100);
/*CURSOR c_curr_code(p_in_po_number IN VARCHAR2, p_in_org_id IN NUMBER) IS
SELECT poh.currency_code
FROM po_headers_all poh
WHERE poh.org_id = p_in_org_id
AND poh.segment1 = p_in_po_number;*/
CURSOR c_period_status(p_trx_date DATE) IS
SELECT (SELECT COUNT(p.status)
FROM ORG_ACCT_PERIODS_V p
WHERE p.organization_id = p_org_id
AND upper(p.status) = upper('Open')
AND trunc(p_trx_date) BETWEEN p.start_date AND p.end_date) inv_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 201
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) pur_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 200
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ap_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 222
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ar_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 101
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) gl_period
FROM dual;
CURSOR update_r(p_in_header_id VARCHAR2) IS
SELECT receipt_header_id, processing_status_code
FROM rcv_headers_interface
WHERE header_interface_id = p_in_header_id;
CURSOR receipt(p_req_id number) IS
select r.receipt_num
from rcv_shipment_headers r
where r.request_id = p_req_id;
CURSOR c_rcv_header IS
/* SELECT c_transaction_id,PO_Number,Organization_ID,WayBill ,Operator,
TRANSACTION_DATE*/
SELECT DISTINCT PO_Number,
Organization_ID,
GRN_Number,
WayBill,
Operator,
transaction_date
--to_char(transaction_date,'MON-YYYY') trx_period -- added in 2011/07/06
FROM BG_CUT_RCV_Interface bcr
WHERE PROCESSING_STATUS = 'P'
AND transaction_type = 'PO'
AND organization_id = p_org_id
ORDER BY transaction_date;
CURSOR c_rcv_po(p_in_po_number IN VARCHAR2, p_in_org_id IN NUMBER) IS
SELECT PO_HEADER_ID, VENDOR_ID, SEGMENT1, ORG_ID, vendor_site_id
FROM PO_HEADERS_ALL
WHERE segment1 = p_in_po_number
AND org_id = p_org_id;
CURSOR c_rcv_cms_oracle(p_po_number IN VARCHAR2,
p_in_waybill IN VARCHAR2,
p_in_trx_date IN DATE) IS
SELECT bcr.c_transaction_id,
bcr.po_number,
bcr.organization_id,
bcr.item_number,
bcr.line_number,
bcr.shipment_line_num,
bcr.grn_number,
bcr.subinventory_code,
bcr.mrb_no,
bcr.rma_number,
bcr.transaction_qty,
bcr.transaction_uom,
bcr.waybill,
bcr.operator /*,
bcr.Last_Update_Date,
bcr.Last_Updated_By,
bcr.creation_date,
bcr.created_by*/
FROM BG_CUT_RCV_Interface bcr
WHERE organization_id = p_org_id
AND bcr.waybill = p_in_waybill
AND bcr.processing_status = 'P'
AND trunc(bcr.transaction_date) = trunc(p_in_trx_date)
AND bcr.transaction_type = 'PO'
AND bcr.po_number = p_po_number
--AND bcr.c_transaction_id = p_transaction_id
ORDER BY bcr.line_number, bcr.shipment_line_num;
CURSOR c_rcv(p_po_header_id IN NUMBER,
p_po_line_number IN NUMBER,
p_shipment_number IN NUMBER,
p_waybill IN VARCHAR2) IS
SELECT pl.org_Id,
PL.ITEM_ID,
pl.po_header_id,
PL.PO_LINE_ID,
PL.LINE_NUM,
PLL.QUANTITY,
PL.UNIT_MEAS_LOOKUP_CODE,
MP.ORGANIZATION_CODE,
PLL.LINE_LOCATION_ID,
PLL.CLOSED_CODE,
PLL.QUANTITY_RECEIVED,
apps.invgrcvk_bg_1.bg_get_receipt_qty(p_po_header_id,
p_po_line_number,
p_shipment_number,
p_waybill) received_qty,
PLL.CANCEL_FLAG,
PLL.SHIPMENT_NUM,
pda.destination_type_code,
pda.deliver_to_person_id,
pll.ship_to_location_id,
pda.destination_subinventory,
pda.destination_organization_id
FROM PO_LINES_ALL PL,
PO_LINE_LOCATIONS_ALL PLL,
MTL_PARAMETERS MP,
apps.po_distributions_all pda
WHERE PL.PO_LINE_ID = PLL.PO_LINE_ID
AND PLL.SHIP_TO_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND pll.line_location_id = pda.line_location_id
AND pll.shipment_num = p_shipment_number
AND pl.line_num = p_po_line_number
AND Pl.PO_HEADER_ID = p_po_header_id; --291502 --X_PO_HEADER_ID
BEGIN
fnd_file.put_line(fnd_file.output,
'***CMS Data to Oracle RCV API Insert Script***');
dbms_output.put_line('***CMS Data to Oracle RCV API Insert Script***');
SELECT fnd_profile.value('USER_ID') INTO v_user_id FROM dual;
v_operator := INVGRCVK_BG_1.bg_get_employee_name(v_user_id);
FOR rec_rcv IN c_rcv_header LOOP
v_receipt_number := '';
v_rcv_h_interface_currval := '';
v_shipment_header_id := '';
v_interface_process_status := '';
OPEN c_period_status(rec_rcv.transaction_date);
FETCH c_period_status
INTO v_inv_period,
v_pur_period,
v_ap_period,
v_ar_period,
v_gl_period;
CLOSE c_period_status;
v_conversion_rate := INVGRCVK_BG_1.bg_get_conv_rate(rec_rcv.PO_Number,
p_org_id,
rec_rcv.TRANSACTION_date);
/* dbms_output.put_line('*00'||v_inv_period);
dbms_output.put_line('*00'||v_pur_period);
dbms_output.put_line('*00'||v_ap_period);
dbms_output.put_line('*00'||v_ar_period);
dbms_output.put_line('*00'||v_gl_period);
dbms_output.put_line('*00'||v_conversion_rate);*/
IF v_inv_period = 1 AND v_pur_period = 1 AND v_ap_period = 1 AND
v_ar_period = 1 AND v_gl_period = 1 AND
v_conversion_rate IS NOT NULL THEN
dbms_output.put_line('*01');
FOR rec_po IN c_rcv_po(rec_rcv.PO_Number, rec_rcv.organization_id) LOOP
INSERT INTO rcv_headers_interface
(header_interface_id,
GROUP_ID,
processing_status_code,
receipt_source_code,
transaction_type,
auto_transact_code,
last_update_date,
last_updated_by,
last_update_login,
creation_date, --receipt date--
created_by,
vendor_id,
vendor_site_id,
expected_receipt_date,
validation_flag,
--SHIPMENT_NUM,
--SHIPPED_DATE,
--PACKING_SLIP,
WAYBILL_AIRBILL_NUM,
--FREIGHT_CARRIER_CODE,
--BILL_OF_LADING,
--NUM_OF_CONTAINERS,
EMPLOYEE_NAME,
ATTRIBUTE_CATEGORY,
attribute1
--COMMENTS
)
VALUES
(rcv_headers_interface_s.NEXTVAL,
rcv_interface_groups_s.NEXTVAL,
'PENDING',
'VENDOR',
'NEW',
'RECEIVE',
SYSDATE,
V_USER_ID,
0,
rec_rcv.TRANSACTION_DATE,
V_USER_ID,
rec_po.vendor_id,
rec_po.vendor_site_id,
SYSDATE,
'Y',
--v_shipment_num, --'123',
--v_SHIPPED_DATE, --to_date('2010/10/01','yyyy/mm/dd'),
--v_packing_slip,--'a',
rec_rcv.WayBill,
--V_Freight_Carrier,--'COMPANY TRUCK',
--V_Bill_Lading,--'Test01',
--V_Container,--1,
v_operator, --rec_rcv.Operator,
p_org_id,
1
--v_comments--'COMMENTS'
);
FOR rec_t IN c_rcv_cms_oracle(rec_rcv.PO_Number,
rec_rcv.waybill,
rec_rcv.transaction_date) LOOP
dbms_output.put_line('*02');
FOR rec IN c_rcv(rec_po.po_header_id,
rec_t.line_number,
rec_t.shipment_line_num,
rec_t.waybill) LOOP
IF rec.closed_code IN ('APPROVED', 'OPEN') AND
rec.QUANTITY_RECEIVED < rec.QUANTITY AND
NVL(rec.CANCEL_FLAG, 'N') = 'N' THEN
INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
po_header_id,
PO_LINE_ID,
ITEM_ID,
QUANTITY,
UNIT_OF_MEASURE,
PO_LINE_LOCATION_ID,
AUTO_TRANSACT_CODE,
RECEIPT_SOURCE_CODE,
TO_ORGANIZATION_CODE,
to_organization_id,
ship_to_location_id,
SOURCE_DOCUMENT_CODE,
destination_type_code,
deliver_to_person_id,
deliver_to_location_id,
subinventory,
DOCUMENT_NUM,
HEADER_INTERFACE_ID,
VALIDATION_FLAG)
VALUES
(RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
RCV_INTERFACE_GROUPS_S.CURRVAL,
SYSDATE,
V_USER_ID,
SYSDATE,
V_USER_ID,
0,
'RECEIVE', --TRANSACTION_TYPE
rec_rcv.TRANSACTION_DATE, --SYSDATE ,
'PENDING',
'BATCH',
'PENDING',
rec.po_header_id,
rec.PO_LINE_ID,
rec.ITEM_ID,
--rec_t.transaction_qty ,
rec.received_qty,
rec_t.transaction_uom, --UNIT_OF_MEASURE
rec.LINE_LOCATION_ID,
'DELIVER',
'VENDOR',
rec.ORGANIZATION_CODE,
rec.org_id,
rec.ship_to_location_id,
'PO',
rec.destination_type_code,
rec.deliver_to_person_id,
rec.ship_to_location_id,
nvl(rec.destination_subinventory,
rec_t.subinventory_code),
rec_rcv.PO_Number,
RCV_HEADERS_INTERFACE_S.CURRVAL,
'Y');
DBMS_OUTPUT.PUT_LINE('PO line: ' || rec.LINE_NUM ||
' Shipment: ' || rec.SHIPMENT_NUM ||
' has been inserted into ROI.');
ELSE
DBMS_OUTPUT.PUT_LINE('PO line ' || rec.LINE_NUM ||
' is either closed, cancelled, received.');
END IF;
END LOOP;
--DBMS_OUTPUT.PUT_LINE('*** ezROI COMPLETE - End ***');
END LOOP;
END LOOP;
fnd_file.put_line(fnd_file.output,
'*** Call Receiving Transaction Processor Reoport ***');
/* fnd_global.apps_initialize(user_id => fnd_profile.value('USER_ID'),
resp_id => fnd_profile.value('RESP_ID'),
resp_appl_id => fnd_profile.value('RESP_APPL_ID'), --GL
security_group_id => 0);*/
l_req_id := fnd_request.submit_request('PO',
'RVCTP',
null,
sysdate,
false,
'BATCH',
null);
dbms_output.put_line('l_req_id: ' || l_req_id);
COMMIT;
IF l_req_id <= 0 THEN
fnd_file.put_line(fnd_file.output,
'*** Receiving Transaction Processor runing exception ***');
ELSIF l_req_id > 0 THEN
fnd_file.put_line(fnd_file.output,
'*** Receiving Transaction Processor Request Submit successful ***');
l_complete_flag := fnd_concurrent.wait_for_request(l_req_id,
1,
3600,
l_phase,
l_status,
l_dev_phase,
l_dev_status,
l_message);
-- use the Customized receipt number
SELECT rcv_headers_interface_s.CURRVAL
INTO v_rcv_h_interface_currval
FROM dual;
fnd_file.put_line(fnd_file.output,
'rcv_headers_interface_s: ' ||
v_rcv_h_interface_currval);
OPEN update_r(v_rcv_h_interface_currval);
FETCH update_r
INTO v_shipment_header_id, v_interface_process_status;
CLOSE update_r;
fnd_file.put_line(fnd_file.output,
'v_shipment_header_id: ' ||
v_shipment_header_id);
fnd_file.put_line(fnd_file.output,
'rec_rcv.grn_number: ' || rec_rcv.grn_number);
IF rec_rcv.grn_number IS NULL AND
v_shipment_header_id IS NOT NULL THEN
SELECT BG_GEN_RCV_NUM(p_org_id, 1)
INTO v_receipt_number
FROM dual;
ELSE
v_receipt_number := rec_rcv.grn_number;
END IF;
IF v_interface_process_status = 'SUCCESS' THEN
UPDATE rcv_shipment_headers
SET receipt_num = v_receipt_number --rec_rcv.grn_number
WHERE shipment_header_id = v_shipment_header_id;
UPDATE BG_CUT_RCV_Interface
SET PROCESSING_STATUS = 'S', grn_number = v_receipt_number
WHERE PROCESSING_STATUS = 'P'
AND trunc(TRANSACTION_DATE) =
trunc(rec_rcv.transaction_date)
AND transaction_type = 'PO'
AND waybill = rec_rcv.waybill
AND po_number = rec_rcv.po_number;
--AND c_transaction_id = rec_rcv.c_transaction_id;
END IF;
COMMIT;
END IF;
IF l_complete_flag THEN
FOR CURSOR3 IN receipt(l_req_id) LOOP
IF CURSOR3.receipt_num IS NULL THEN
--** check the po_interface_errors*--
select count(*)
into v_count
from po_interface_errors a
where a.request_id = l_req_id;
fnd_file.put_line(fnd_file.output,
'The po_interface_errors exists error , you can runing the report "Receiving Interface Errors Report" to check !' ||
CURSOR3.receipt_num);
DBMS_OUTPUT.PUT_LINE('The po_interface_errors exists error , you can runing the report "Receiving Interface Errors Report" to check !' ||
CURSOR3.receipt_num);
--** check the po_interface_errors*--
ELSE
fnd_file.put_line(fnd_file.output,
'Receipt Number:' || CURSOR3.receipt_num);
DBMS_OUTPUT.PUT_LINE('Receipt Number:' ||
CURSOR3.receipt_num);
END IF;
END LOOP;
ELSE
fnd_file.put_line(fnd_file.output,
'*** Receiving Transaction Processing ***');
DBMS_OUTPUT.PUT_LINE('*** Receiving Transaction Processing ***');
END IF;
ELSIF v_conversion_rate IS NULL THEN
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'The PO number: ' || rec_rcv.po_number ||
' have no conversion rate in ' ||
to_char(rec_rcv.transaction_date, 'yyyy/mm/dd'));
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
ELSE
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'*** The period is not opened! *** Period: ' ||
to_char(rec_rcv.transaction_date, 'MON-YYYY'));
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
END IF;
v_conversion_rate := NULL;
END LOOP;
COMMIT;
/*
fnd_file.put_line(fnd_file.output, '*** Call Receiving Transaction Processor Reoport ***');
fnd_global.apps_initialize(user_id => fnd_profile.value('USER_ID'),
resp_id => fnd_profile.value('RESP_ID'),
resp_appl_id => fnd_profile.value('RESP_APPL_ID'), --GL
security_group_id => 0);
l_req_id := fnd_request.submit_request('PO',
'RVCTP',
null,
sysdate,
false,
'BATCH',
null);
dbms_output.put_line('l_req_id: '||l_req_id);
COMMIT;
IF l_req_id <= 0 THEN
fnd_file.put_line(fnd_file.output,'*** Receiving Transaction Processor runing exception ***');
ELSIF l_req_id > 0 THEN
fnd_file.put_line(fnd_file.output,'*** Receiving Transaction Processor Request Submit successful ***');
COMMIT;
l_complete_flag := fnd_concurrent.wait_for_request(l_req_id,
1,
3600,
l_phase,
l_status,
l_dev_phase,
l_dev_status,
l_message);
END IF;
IF l_complete_flag THEN
FOR CURSOR3 IN receipt(l_req_id) LOOP
IF CURSOR3.receipt_num IS NULL THEN
--** check the po_interface_errors*--
select count(*)into v_count
from po_interface_errors a where a.request_id=l_req_id;
fnd_file.put_line(fnd_file.output,'The po_interface_errors exists error , you can runing the report "Receiving Interface Errors Report" to check !' || CURSOR3.receipt_num);
DBMS_OUTPUT.PUT_LINE('The po_interface_errors exists error , you can runing the report "Receiving Interface Errors Report" to check !' || CURSOR3.receipt_num);
--** check the po_interface_errors*--
ELSE
fnd_file.put_line(fnd_file.output,'Receipt Number:' || CURSOR3.receipt_num);
DBMS_OUTPUT.PUT_LINE('Receipt Number:' || CURSOR3.receipt_num);
END IF;
END LOOP;
ELSE
fnd_file.put_line(fnd_file.output,'*** Receiving Transaction Processing ***');
DBMS_OUTPUT.PUT_LINE('*** Receiving Transaction Processing ***');
END IF;*/
END bg_po_receipt_sysauto;
PROCEDURE bg_rtv_sysauto(p_org_id IN NUMBER) IS
v_user_id number;
v_group_id number;
l_request_id number;
l_request_id2 number;
v_inv_period NUMBER;
v_pur_period NUMBER;
v_ap_period NUMBER;
v_ar_period NUMBER;
v_gl_period NUMBER;
CURSOR c_period_status(p_trx_date DATE) IS
SELECT (SELECT COUNT(p.status)
FROM ORG_ACCT_PERIODS_V p
WHERE p.organization_id = p_org_id
AND upper(p.status) = upper('Open')
AND trunc(p_trx_date) BETWEEN p.start_date AND p.end_date) inv_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 201
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) pur_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 200
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ap_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 222
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ar_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 101
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) gl_period
FROM dual;
CURSOR c_cms_rtv IS
SELECT bcr.c_transaction_id,
bcr.po_number,
bcr.item_number,
bcr.line_number,
bcr.shipment_line_num,
bcr.transaction_qty,
bcr.transaction_uom,
bcr.transaction_date,
bcr.grn_number,
bcr.subinventory_code,
bcr.mrb_no,
bcr.operator
FROM BG_CUT_RCV_Interface bcr
WHERE 1 = 1
AND bcr.transaction_type = 'RTV'
AND bcr.processing_status = 'P'
AND bcr.organization_id = p_org_id
ORDER BY bcr.transaction_date;
CURSOR c_po_info(p_po_number IN VARCHAR2,
p_line_number IN NUMBER,
p_shipment_line_number IN NUMBER) IS
SELECT pl.org_Id,
PL.ITEM_ID,
pl.po_header_id,
PL.PO_LINE_ID,
PL.LINE_NUM,
PLL.QUANTITY,
PL.UNIT_MEAS_LOOKUP_CODE,
MP.ORGANIZATION_CODE,
PLL.LINE_LOCATION_ID,
PLL.CLOSED_CODE,
PLL.QUANTITY_RECEIVED,
PLL.CANCEL_FLAG,
PLL.SHIPMENT_NUM,
pda.destination_type_code,
pda.deliver_to_person_id,
pll.ship_to_location_id,
pda.destination_subinventory,
pda.destination_organization_id
FROM PO_LINE_LOCATIONS_ALL PLL,
MTL_PARAMETERS MP,
apps.po_distributions_all pda,
PO_LINES_ALL PL,
po_headers_all poh
WHERE PL.PO_LINE_ID = PLL.PO_LINE_ID
AND pll.shipment_num = p_shipment_line_number
AND pl.line_num = p_line_number
AND PLL.SHIP_TO_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND pll.line_location_id = pda.line_location_id
AND pl.po_header_id = poh.po_header_id
AND pl.org_id = poh.org_id
AND poh.Type_Lookup_Code = 'STANDARD'
AND poh.org_id = p_org_id
AND poh.segment1 = p_po_number; --'BSZ20110217';
CURSOR c_rcv_info(p_po_header_id IN NUMBER,
p_po_line_id IN NUMBER,
p_line_location_id IN NUMBER,
p_grn_number IN VARCHAR2) IS
SELECT rt.transaction_id,
rt.group_id,
rt.po_header_id,
rt.po_line_id,
rt.po_line_location_id,
rt.po_distribution_id,
rt.organization_id,
rt.vendor_id,
rt.vendor_site_id,
rt.unit_of_measure,
rt.destination_type_code,
rt.subinventory,
rt.shipment_header_id,
rt.routing_header_id,
rt.shipment_line_id,
rt.primary_quantity,
rsh.expected_receipt_date
FROM rcv_shipment_headers rsh, rcv_transactions rt
WHERE rt.shipment_header_id = rsh.shipment_header_id
AND rt.transaction_type = 'DELIVER'
AND rsh.receipt_num = p_grn_number --'922748'
AND rt.po_line_location_id = p_line_location_id --768212
AND rt.po_line_id = p_po_line_id --744434
AND rt.po_header_id = p_po_header_id; --340266;
BEGIN
v_user_id := fnd_global.USER_ID;
Select RCV_INTERFACE_GROUPS_S.nextval into v_group_id from dual;
FOR c_rtv IN c_cms_rtv LOOP
OPEN c_period_status(c_rtv.transaction_date);
FETCH c_period_status
INTO v_inv_period,
v_pur_period,
v_ap_period,
v_ar_period,
v_gl_period;
CLOSE c_period_status;
IF v_inv_period = 1 AND v_pur_period = 1 AND v_ap_period = 1 AND
v_ar_period = 1 AND v_gl_period = 1 THEN
FOR c_po IN c_po_info(c_rtv.po_number,
c_rtv.line_number,
c_rtv.shipment_line_num) LOOP
FOR rec IN c_rcv_info(c_po.po_header_id,
c_po.po_line_id,
c_po.line_location_id,
c_rtv.grn_number) LOOP
INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
PRIMARY_QUANTITY,
UNIT_OF_MEASURE,
ITEM_ID,
ITEM_DESCRIPTION,
EMPLOYEE_ID,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
Routing_Header_Id,
RECEIPT_SOURCE_CODE,
Interface_Source_Code,
VENDOR_ID,
VENDOR_SITE_ID,
FROM_ORGANIZATION_ID,
FROM_SUBINVENTORY,
SUBINVENTORY,
TO_ORGANIZATION_ID,
FROM_LOCATOR_ID,
EXPECTED_RECEIPT_DATE,
SOURCE_DOCUMENT_CODE,
PARENT_TRANSACTION_ID,
PO_HEADER_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
PO_DISTRIBUTION_ID,
DESTINATION_TYPE_CODE,
DESTINATION_CONTEXT,
DELIVER_TO_PERSON_ID,
LOCATION_ID,
DELIVER_TO_LOCATION_ID,
VALIDATION_FLAG)
VALUES
(rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
v_group_id, --rcv_interface_groups_s.nextval, --GROUP_ID
SYSDATE, --LAST_UPDATE_DATE
v_user_id, --LAST_UPDATE_BY
SYSDATE, --CREATION_DATE
v_user_id, --CREATED_BY
v_user_id, --LAST_UPDATE_LOGIN
'RETURN TO VENDOR', --TRANSACTION_TYPE
c_rtv.transaction_date, --SYSDATE, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'IMMEDIATE', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
c_rtv.transaction_qty, --c_receipt.primary_quantity, --QUANTITY
c_rtv.transaction_qty, --c_receipt.primary_quantity, --PRIMARY_QUANTITY
c_rtv.transaction_uom, --c_receipt.unit_of_measure, --UNIT_OF_MEASURE
Null, --ITEM_ID
Null,
0, --EMPLOYEE_ID
rec.shipment_header_id, -- SHIPMENT_HEADER_ID
rec.shipment_line_id, -- SHIPMENT_LINE_ID
rec.routing_header_id, -- Routing_Header_Id
'VENDOR', --RECEIPT_SOURCE_CODE
'RCV', -- Interface_Source_Code
rec.vendor_id, -- VENDOR_ID
rec.vendor_site_id, -- VENDOR_SITE_ID
rec.organization_id, -- FROM_ORGANIZATION_ID
NULL, --'NA01' --FROM_SUBINVENTORY
c_rtv.subinventory_code, -- subinventory
rec.organization_id, -- TO_ORGANIZATION_ID
null, --FROM_LOCATOR_ID
rec.expected_receipt_date, --EXPECTED_RECEIPT_DATE
'PO', --SOURCE_DOCUMENT_CODE
rec.transaction_id, -- PARENT_TRANSACTION_ID
rec.po_header_id, -- PO_HEADER_ID
rec.po_line_id, -- PO_LINE_ID
rec.po_line_location_id, -- PO_LINE_LOCATION_ID
rec.po_distribution_id, -- PO_DISTRIBUTION_ID
'RECEIVING', --rec.destination_type_code, --'RECEIVING', --DESTINATION_TYPE_CODE
rec.destination_type_code, --DESTINATION_CONTEXT
null, --DELIVER_TO_PERSON_ID
null, --LOCATION_ID
null, --DELIVER_TO_LOCATION_ID
'Y' --VALIDATION_FLAG
);
END LOOP;
END LOOP;
UPDATE bg_cut_rcv_interface
SET processing_status = 'S'
WHERE processing_status = 'P'
AND transaction_type = 'RTV'
AND c_transaction_id = c_rtv.c_transaction_id;
ELSE
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'*** The period is not opened! *** Period: ' ||
to_char(c_rtv.transaction_date, 'MON-YYYY'));
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
END IF;
END LOOP;
COMMIT;
l_request_id := fnd_request.submit_request('PO', --APPLICATIONI SHORT NAME;
'RVCTP', --PROGRAME SHORT NAME;
'',
'',
FALSE,
'IMMEDIATE',
v_group_id
--104--104,
--CHR(0)
);
l_request_id2 := fnd_request.submit_request('PO', --APPLICATIONI SHORT NAME;
'RCVDLPDT', --PROGRAME SHORT NAME;
'',
'',
FALSE,
'P_group_id=' || v_group_id,
'P_receipt_source_type=Supplier',
'P_qty_precision=2',
'P_org_id=' || p_org_id);
FND_FILE.put_line(2,
'Receiving Transaction Processor: ' || l_request_id ||
' Receipt Traveller Concurrent program: ' ||
l_request_id2);
FND_FILE.put_line(2, 'Group Id: ' || v_group_id);
COMMIT;
END bg_rtv_sysauto;
v_user_id number;
v_group_id number;
l_request_id number;
l_request_id2 number;
v_inv_period NUMBER;
v_pur_period NUMBER;
v_ap_period NUMBER;
v_ar_period NUMBER;
v_gl_period NUMBER;
CURSOR c_period_status(p_trx_date DATE) IS
SELECT (SELECT COUNT(p.status)
FROM ORG_ACCT_PERIODS_V p
WHERE p.organization_id = p_org_id
AND upper(p.status) = upper('Open')
AND trunc(p_trx_date) BETWEEN p.start_date AND p.end_date) inv_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 201
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) pur_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 200
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ap_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 222
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ar_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 101
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) gl_period
FROM dual;
CURSOR c_cms_rtv IS
SELECT bcr.c_transaction_id,
bcr.po_number,
bcr.item_number,
bcr.line_number,
bcr.shipment_line_num,
bcr.transaction_qty,
bcr.transaction_uom,
bcr.transaction_date,
bcr.grn_number,
bcr.subinventory_code,
bcr.mrb_no,
bcr.operator
FROM BG_CUT_RCV_Interface bcr
WHERE 1 = 1
AND bcr.transaction_type = 'RTV'
AND bcr.processing_status = 'P'
AND bcr.organization_id = p_org_id
ORDER BY bcr.transaction_date;
CURSOR c_po_info(p_po_number IN VARCHAR2,
p_line_number IN NUMBER,
p_shipment_line_number IN NUMBER) IS
SELECT pl.org_Id,
PL.ITEM_ID,
pl.po_header_id,
PL.PO_LINE_ID,
PL.LINE_NUM,
PLL.QUANTITY,
PL.UNIT_MEAS_LOOKUP_CODE,
MP.ORGANIZATION_CODE,
PLL.LINE_LOCATION_ID,
PLL.CLOSED_CODE,
PLL.QUANTITY_RECEIVED,
PLL.CANCEL_FLAG,
PLL.SHIPMENT_NUM,
pda.destination_type_code,
pda.deliver_to_person_id,
pll.ship_to_location_id,
pda.destination_subinventory,
pda.destination_organization_id
FROM PO_LINE_LOCATIONS_ALL PLL,
MTL_PARAMETERS MP,
apps.po_distributions_all pda,
PO_LINES_ALL PL,
po_headers_all poh
WHERE PL.PO_LINE_ID = PLL.PO_LINE_ID
AND pll.shipment_num = p_shipment_line_number
AND pl.line_num = p_line_number
AND PLL.SHIP_TO_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND pll.line_location_id = pda.line_location_id
AND pl.po_header_id = poh.po_header_id
AND pl.org_id = poh.org_id
AND poh.Type_Lookup_Code = 'STANDARD'
AND poh.org_id = p_org_id
AND poh.segment1 = p_po_number; --'BSZ20110217';
CURSOR c_rcv_info(p_po_header_id IN NUMBER,
p_po_line_id IN NUMBER,
p_line_location_id IN NUMBER,
p_grn_number IN VARCHAR2) IS
SELECT rt.transaction_id,
rt.group_id,
rt.po_header_id,
rt.po_line_id,
rt.po_line_location_id,
rt.po_distribution_id,
rt.organization_id,
rt.vendor_id,
rt.vendor_site_id,
rt.unit_of_measure,
rt.destination_type_code,
rt.subinventory,
rt.shipment_header_id,
rt.routing_header_id,
rt.shipment_line_id,
rt.primary_quantity,
rsh.expected_receipt_date
FROM rcv_shipment_headers rsh, rcv_transactions rt
WHERE rt.shipment_header_id = rsh.shipment_header_id
AND rt.transaction_type = 'DELIVER'
AND rsh.receipt_num = p_grn_number --'922748'
AND rt.po_line_location_id = p_line_location_id --768212
AND rt.po_line_id = p_po_line_id --744434
AND rt.po_header_id = p_po_header_id; --340266;
BEGIN
v_user_id := fnd_global.USER_ID;
Select RCV_INTERFACE_GROUPS_S.nextval into v_group_id from dual;
FOR c_rtv IN c_cms_rtv LOOP
OPEN c_period_status(c_rtv.transaction_date);
FETCH c_period_status
INTO v_inv_period,
v_pur_period,
v_ap_period,
v_ar_period,
v_gl_period;
CLOSE c_period_status;
IF v_inv_period = 1 AND v_pur_period = 1 AND v_ap_period = 1 AND
v_ar_period = 1 AND v_gl_period = 1 THEN
FOR c_po IN c_po_info(c_rtv.po_number,
c_rtv.line_number,
c_rtv.shipment_line_num) LOOP
FOR rec IN c_rcv_info(c_po.po_header_id,
c_po.po_line_id,
c_po.line_location_id,
c_rtv.grn_number) LOOP
INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
PRIMARY_QUANTITY,
UNIT_OF_MEASURE,
ITEM_ID,
ITEM_DESCRIPTION,
EMPLOYEE_ID,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
Routing_Header_Id,
RECEIPT_SOURCE_CODE,
Interface_Source_Code,
VENDOR_ID,
VENDOR_SITE_ID,
FROM_ORGANIZATION_ID,
FROM_SUBINVENTORY,
SUBINVENTORY,
TO_ORGANIZATION_ID,
FROM_LOCATOR_ID,
EXPECTED_RECEIPT_DATE,
SOURCE_DOCUMENT_CODE,
PARENT_TRANSACTION_ID,
PO_HEADER_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
PO_DISTRIBUTION_ID,
DESTINATION_TYPE_CODE,
DESTINATION_CONTEXT,
DELIVER_TO_PERSON_ID,
LOCATION_ID,
DELIVER_TO_LOCATION_ID,
VALIDATION_FLAG)
VALUES
(rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
v_group_id, --rcv_interface_groups_s.nextval, --GROUP_ID
SYSDATE, --LAST_UPDATE_DATE
v_user_id, --LAST_UPDATE_BY
SYSDATE, --CREATION_DATE
v_user_id, --CREATED_BY
v_user_id, --LAST_UPDATE_LOGIN
'RETURN TO VENDOR', --TRANSACTION_TYPE
c_rtv.transaction_date, --SYSDATE, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'IMMEDIATE', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
c_rtv.transaction_qty, --c_receipt.primary_quantity, --QUANTITY
c_rtv.transaction_qty, --c_receipt.primary_quantity, --PRIMARY_QUANTITY
c_rtv.transaction_uom, --c_receipt.unit_of_measure, --UNIT_OF_MEASURE
Null, --ITEM_ID
Null,
0, --EMPLOYEE_ID
rec.shipment_header_id, -- SHIPMENT_HEADER_ID
rec.shipment_line_id, -- SHIPMENT_LINE_ID
rec.routing_header_id, -- Routing_Header_Id
'VENDOR', --RECEIPT_SOURCE_CODE
'RCV', -- Interface_Source_Code
rec.vendor_id, -- VENDOR_ID
rec.vendor_site_id, -- VENDOR_SITE_ID
rec.organization_id, -- FROM_ORGANIZATION_ID
NULL, --'NA01' --FROM_SUBINVENTORY
c_rtv.subinventory_code, -- subinventory
rec.organization_id, -- TO_ORGANIZATION_ID
null, --FROM_LOCATOR_ID
rec.expected_receipt_date, --EXPECTED_RECEIPT_DATE
'PO', --SOURCE_DOCUMENT_CODE
rec.transaction_id, -- PARENT_TRANSACTION_ID
rec.po_header_id, -- PO_HEADER_ID
rec.po_line_id, -- PO_LINE_ID
rec.po_line_location_id, -- PO_LINE_LOCATION_ID
rec.po_distribution_id, -- PO_DISTRIBUTION_ID
'RECEIVING', --rec.destination_type_code, --'RECEIVING', --DESTINATION_TYPE_CODE
rec.destination_type_code, --DESTINATION_CONTEXT
null, --DELIVER_TO_PERSON_ID
null, --LOCATION_ID
null, --DELIVER_TO_LOCATION_ID
'Y' --VALIDATION_FLAG
);
END LOOP;
END LOOP;
UPDATE bg_cut_rcv_interface
SET processing_status = 'S'
WHERE processing_status = 'P'
AND transaction_type = 'RTV'
AND c_transaction_id = c_rtv.c_transaction_id;
ELSE
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'*** The period is not opened! *** Period: ' ||
to_char(c_rtv.transaction_date, 'MON-YYYY'));
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
END IF;
END LOOP;
COMMIT;
l_request_id := fnd_request.submit_request('PO', --APPLICATIONI SHORT NAME;
'RVCTP', --PROGRAME SHORT NAME;
'',
'',
FALSE,
'IMMEDIATE',
v_group_id
--104--104,
--CHR(0)
);
l_request_id2 := fnd_request.submit_request('PO', --APPLICATIONI SHORT NAME;
'RCVDLPDT', --PROGRAME SHORT NAME;
'',
'',
FALSE,
'P_group_id=' || v_group_id,
'P_receipt_source_type=Supplier',
'P_qty_precision=2',
'P_org_id=' || p_org_id);
FND_FILE.put_line(2,
'Receiving Transaction Processor: ' || l_request_id ||
' Receipt Traveller Concurrent program: ' ||
l_request_id2);
FND_FILE.put_line(2, 'Group Id: ' || v_group_id);
COMMIT;
END bg_rtv_sysauto;
PROCEDURE bg_mtl_sysauto(p_org_id IN NUMBER) IS
v_source_line_id NUMBER := 1;
v_source_header_id NUMBER := 1;
v_reason_id mtl_transaction_reasons.reason_id%TYPE;
v_primary_qty NUMBER;
v_inv_period NUMBER;
v_pur_period NUMBER;
v_ap_period NUMBER;
v_ar_period NUMBER;
v_gl_period NUMBER;
CURSOR c_period_status(p_trx_date DATE) IS
SELECT (SELECT COUNT(p.status)
FROM ORG_ACCT_PERIODS_V p
WHERE p.organization_id = p_org_id
AND upper(p.status) = upper('Open')
AND trunc(p_trx_date) BETWEEN p.start_date AND p.end_date) inv_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 201
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) pur_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 200
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ap_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 222
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ar_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 101
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) gl_period
FROM dual;
CURSOR c_mtl IS
SELECT bcm.c_transaction_id,
bcm.Organization_Id,
msi.inventory_item_id,
bcm.item_number,
bcm.transaction_qty,
bcm.transaction_uom,
msi.primary_uom_code,
bcm.subinventory_code,
bcm.transaction_subinventory,
bcm.transaction_date,
bcm.operator,
bcm.reason_name,
bcm.reference,
bcm.document_no,
bcm.mrb_no
FROM mtl_system_items_b msi, bg_cut_mtl_interface bcm
WHERE msi.segment1 = bcm.item_number
AND msi.organization_Id = bcm.Organization_Id
AND bcm.transaction_type = 'MTL'
AND bcm.processing_status = 'P'
AND bcm.Organization_Id = p_org_id
ORDER BY bcm.transaction_date;
CURSOR c_reason(p_reason_name IN VARCHAR2) IS
SELECT reason_id
FROM mtl_transaction_reasons
WHERE reason_name = p_reason_name;
n_txn_hdr_id number;
n_INTERFACE_ID number;
n_proces_txn number;
x_msg_count number;
x_trans_count number;
x_msg_data varchar2(20000);
x_returnstatus VARCHAR2(4000);
x_errormsg VARCHAR2(4000);
BEGIN
FOR rec IN c_mtl LOOP
OPEN c_period_status(rec.transaction_date);
FETCH c_period_status
INTO v_inv_period,
v_pur_period,
v_ap_period,
v_ar_period,
v_gl_period;
CLOSE c_period_status;
IF v_inv_period = 1 AND v_pur_period = 1 AND v_ap_period = 1 AND
v_ar_period = 1 AND v_gl_period = 1 THEN
SELECT INV_CONVERT.inv_um_convert(rec.inventory_item_id,
2,
rec.transaction_qty,
rec.transaction_uom,
rec.primary_uom_code,
NULL,
NULL)
INTO v_primary_qty
FROM dual;
/* dbms_output.put_line('inventory_item_id' || rec.inventory_item_id ||
rec.transaction_qty || rec.transaction_uom ||
rec.primary_uom_code);*/
IF v_primary_qty <> -99999 THEN
IF rec.reason_name IS NOT NULL THEN
OPEN c_reason(rec.reason_name);
FETCH c_reason
INTO v_reason_id;
CLOSE c_reason;
END IF;
-- dbms_output.put_line('Insert data to mtl_transactions_interface!');
SELECT BG_MTL_TXN_HEADER_S.NEXTVAL INTO n_txn_hdr_id FROM dual;
SELECT BG_MTL_TXN_INTERFACE_S.NEXTVAL
INTO n_INTERFACE_ID
FROM dual;
INSERT INTO mtl_transactions_interface
(TRANSACTION_INTERFACE_ID, -- 1
TRANSACTION_HEADER_ID, -- 2
SOURCE_CODE, -- 3
SOURCE_LINE_ID, -- 4
SOURCE_HEADER_ID, -- 5
PROCESS_FLAG, -- 6
TRANSACTION_MODE, -- 7
-- LOCK_FLAG, -- 8
LAST_UPDATE_DATE, -- 9
LAST_UPDATED_BY, -- 10
CREATION_DATE, --11
CREATED_BY, -- 12
LAST_UPDATE_LOGIN, -- 13
INVENTORY_ITEM_ID, -- 14
ITEM_SEGMENT1, -- 15
ORGANIZATION_ID, -- 16
TRANSACTION_QUANTITY, -- 17
PRIMARY_QUANTITY, -- 18
TRANSACTION_UOM, -- 19
TRANSACTION_DATE, -- 20
ACCT_PERIOD_ID, -- 21
SUBINVENTORY_CODE, -- 22
TRANSACTION_SOURCE_NAME, -- 23
TRANSACTION_SOURCE_TYPE_ID, -- 24 :13
TRANSACTION_ACTION_ID, -- 25:2
TRANSACTION_TYPE_ID, -- 26:2
REASON_ID, --27
TRANSACTION_REFERENCE, -- 28
DISTRIBUTION_ACCOUNT_ID, -- 29
TRANSFER_SUBINVENTORY, -- 30
TRANSFER_ORGANIZATION, -- 31
ATTRIBUTE_CATEGORY, --32
attribute1, --33
attribute2, --34
attribute12, --35
COST_GROUP_ID --36
)
VALUES
(n_INTERFACE_ID,
n_txn_hdr_id,
'BG_CUT', --3
0, --v_source_line_id, --4
0, --v_source_header_id, --5
1, -- PROCESS_FLAG --6
3, --7--3-background 2-immediate
-- 2, --8
SYSDATE, -- 9--last_update_date
fnd_global.user_id, -- 10
SYSDATE, --11
fnd_global.user_id, --12
1, --13 --FND_GLOBAL.CONC_LOGIN_ID, --12
rec.inventory_item_id, --14
rec.item_number, --15
rec.organization_id, --16
rec.transaction_qty, --17
v_primary_qty, --18 PRIMARY_QUANTITY
rec.transaction_uom, --19
rec.transaction_date, --20
NULL, --21
rec.subinventory_code, --22
NULL, --23
13, --24
2, --2:Subinventory transfer --25
2, --2:Subinventory transfer- --26
v_reason_id, --27--32 INV - Subinventory Transfer
rec.reference, --28
NULL, --29 DISTRIBUTION_ACCOUNT_ID
rec.transaction_subinventory, --30
rec.organization_id, --31
rec.organization_id, --32
'N/A', --33
rec.document_no, --34
rec.mrb_no, --35
NULL --36
);
begin
n_proces_txn := inv_txn_manager_pub.process_transactions(p_api_version => 1,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_returnstatus,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_trans_count => x_trans_count,
p_table => 1,
p_header_id => n_txn_hdr_id);
dbms_output.put_line('Process - inv_txn_manager_pub [Transaction Header Id:' ||
n_txn_hdr_id || ']Completed');
dbms_output.put_line('Return Status : ' || x_returnstatus ||
'Completed');
dbms_output.put_line('Return Error :' || x_msg_data ||
'Completed');
UPDATE bg_cut_mtl_interface
SET processing_status = 'S'
WHERE processing_status = 'P'
AND c_transaction_id = rec.c_transaction_id;
exception
when others then
dbms_output.put_line('Process - inv_txn_manager_pub [Transaction Header Id:' ||
n_txn_hdr_id || ']Fail');
dbms_output.put_line('Return Status :' || x_returnstatus ||
'Fail');
dbms_output.put_line('Return Error : ' || x_msg_data ||
'Fail');
UPDATE bg_cut_mtl_interface
SET processing_status = 'E'
WHERE processing_status = 'P'
AND c_transaction_id = rec.c_transaction_id;
end;
END IF;
ELSE
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'*** The period is not opened! *** Period: ' ||
to_char(rec.transaction_date, 'MON-YYYY'));
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
END IF;
END LOOP;
COMMIT;
END bg_mtl_sysauto;
v_source_line_id NUMBER := 1;
v_source_header_id NUMBER := 1;
v_reason_id mtl_transaction_reasons.reason_id%TYPE;
v_primary_qty NUMBER;
v_inv_period NUMBER;
v_pur_period NUMBER;
v_ap_period NUMBER;
v_ar_period NUMBER;
v_gl_period NUMBER;
CURSOR c_period_status(p_trx_date DATE) IS
SELECT (SELECT COUNT(p.status)
FROM ORG_ACCT_PERIODS_V p
WHERE p.organization_id = p_org_id
AND upper(p.status) = upper('Open')
AND trunc(p_trx_date) BETWEEN p.start_date AND p.end_date) inv_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 201
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) pur_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 200
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ap_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 222
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ar_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 101
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) gl_period
FROM dual;
CURSOR c_mtl IS
SELECT bcm.c_transaction_id,
bcm.Organization_Id,
msi.inventory_item_id,
bcm.item_number,
bcm.transaction_qty,
bcm.transaction_uom,
msi.primary_uom_code,
bcm.subinventory_code,
bcm.transaction_subinventory,
bcm.transaction_date,
bcm.operator,
bcm.reason_name,
bcm.reference,
bcm.document_no,
bcm.mrb_no
FROM mtl_system_items_b msi, bg_cut_mtl_interface bcm
WHERE msi.segment1 = bcm.item_number
AND msi.organization_Id = bcm.Organization_Id
AND bcm.transaction_type = 'MTL'
AND bcm.processing_status = 'P'
AND bcm.Organization_Id = p_org_id
ORDER BY bcm.transaction_date;
CURSOR c_reason(p_reason_name IN VARCHAR2) IS
SELECT reason_id
FROM mtl_transaction_reasons
WHERE reason_name = p_reason_name;
n_txn_hdr_id number;
n_INTERFACE_ID number;
n_proces_txn number;
x_msg_count number;
x_trans_count number;
x_msg_data varchar2(20000);
x_returnstatus VARCHAR2(4000);
x_errormsg VARCHAR2(4000);
BEGIN
FOR rec IN c_mtl LOOP
OPEN c_period_status(rec.transaction_date);
FETCH c_period_status
INTO v_inv_period,
v_pur_period,
v_ap_period,
v_ar_period,
v_gl_period;
CLOSE c_period_status;
IF v_inv_period = 1 AND v_pur_period = 1 AND v_ap_period = 1 AND
v_ar_period = 1 AND v_gl_period = 1 THEN
SELECT INV_CONVERT.inv_um_convert(rec.inventory_item_id,
2,
rec.transaction_qty,
rec.transaction_uom,
rec.primary_uom_code,
NULL,
NULL)
INTO v_primary_qty
FROM dual;
/* dbms_output.put_line('inventory_item_id' || rec.inventory_item_id ||
rec.transaction_qty || rec.transaction_uom ||
rec.primary_uom_code);*/
IF v_primary_qty <> -99999 THEN
IF rec.reason_name IS NOT NULL THEN
OPEN c_reason(rec.reason_name);
FETCH c_reason
INTO v_reason_id;
CLOSE c_reason;
END IF;
-- dbms_output.put_line('Insert data to mtl_transactions_interface!');
SELECT BG_MTL_TXN_HEADER_S.NEXTVAL INTO n_txn_hdr_id FROM dual;
SELECT BG_MTL_TXN_INTERFACE_S.NEXTVAL
INTO n_INTERFACE_ID
FROM dual;
INSERT INTO mtl_transactions_interface
(TRANSACTION_INTERFACE_ID, -- 1
TRANSACTION_HEADER_ID, -- 2
SOURCE_CODE, -- 3
SOURCE_LINE_ID, -- 4
SOURCE_HEADER_ID, -- 5
PROCESS_FLAG, -- 6
TRANSACTION_MODE, -- 7
-- LOCK_FLAG, -- 8
LAST_UPDATE_DATE, -- 9
LAST_UPDATED_BY, -- 10
CREATION_DATE, --11
CREATED_BY, -- 12
LAST_UPDATE_LOGIN, -- 13
INVENTORY_ITEM_ID, -- 14
ITEM_SEGMENT1, -- 15
ORGANIZATION_ID, -- 16
TRANSACTION_QUANTITY, -- 17
PRIMARY_QUANTITY, -- 18
TRANSACTION_UOM, -- 19
TRANSACTION_DATE, -- 20
ACCT_PERIOD_ID, -- 21
SUBINVENTORY_CODE, -- 22
TRANSACTION_SOURCE_NAME, -- 23
TRANSACTION_SOURCE_TYPE_ID, -- 24 :13
TRANSACTION_ACTION_ID, -- 25:2
TRANSACTION_TYPE_ID, -- 26:2
REASON_ID, --27
TRANSACTION_REFERENCE, -- 28
DISTRIBUTION_ACCOUNT_ID, -- 29
TRANSFER_SUBINVENTORY, -- 30
TRANSFER_ORGANIZATION, -- 31
ATTRIBUTE_CATEGORY, --32
attribute1, --33
attribute2, --34
attribute12, --35
COST_GROUP_ID --36
)
VALUES
(n_INTERFACE_ID,
n_txn_hdr_id,
'BG_CUT', --3
0, --v_source_line_id, --4
0, --v_source_header_id, --5
1, -- PROCESS_FLAG --6
3, --7--3-background 2-immediate
-- 2, --8
SYSDATE, -- 9--last_update_date
fnd_global.user_id, -- 10
SYSDATE, --11
fnd_global.user_id, --12
1, --13 --FND_GLOBAL.CONC_LOGIN_ID, --12
rec.inventory_item_id, --14
rec.item_number, --15
rec.organization_id, --16
rec.transaction_qty, --17
v_primary_qty, --18 PRIMARY_QUANTITY
rec.transaction_uom, --19
rec.transaction_date, --20
NULL, --21
rec.subinventory_code, --22
NULL, --23
13, --24
2, --2:Subinventory transfer --25
2, --2:Subinventory transfer- --26
v_reason_id, --27--32 INV - Subinventory Transfer
rec.reference, --28
NULL, --29 DISTRIBUTION_ACCOUNT_ID
rec.transaction_subinventory, --30
rec.organization_id, --31
rec.organization_id, --32
'N/A', --33
rec.document_no, --34
rec.mrb_no, --35
NULL --36
);
begin
n_proces_txn := inv_txn_manager_pub.process_transactions(p_api_version => 1,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_returnstatus,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_trans_count => x_trans_count,
p_table => 1,
p_header_id => n_txn_hdr_id);
dbms_output.put_line('Process - inv_txn_manager_pub [Transaction Header Id:' ||
n_txn_hdr_id || ']Completed');
dbms_output.put_line('Return Status : ' || x_returnstatus ||
'Completed');
dbms_output.put_line('Return Error :' || x_msg_data ||
'Completed');
UPDATE bg_cut_mtl_interface
SET processing_status = 'S'
WHERE processing_status = 'P'
AND c_transaction_id = rec.c_transaction_id;
exception
when others then
dbms_output.put_line('Process - inv_txn_manager_pub [Transaction Header Id:' ||
n_txn_hdr_id || ']Fail');
dbms_output.put_line('Return Status :' || x_returnstatus ||
'Fail');
dbms_output.put_line('Return Error : ' || x_msg_data ||
'Fail');
UPDATE bg_cut_mtl_interface
SET processing_status = 'E'
WHERE processing_status = 'P'
AND c_transaction_id = rec.c_transaction_id;
end;
END IF;
ELSE
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'*** The period is not opened! *** Period: ' ||
to_char(rec.transaction_date, 'MON-YYYY'));
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
END IF;
END LOOP;
COMMIT;
END bg_mtl_sysauto;
---Job Complete--
PROCEDURE bg_wip_sysauto(p_org_id IN NUMBER) IS
v_inv_period NUMBER;
v_pur_period NUMBER;
v_ap_period NUMBER;
v_ar_period NUMBER;
v_gl_period NUMBER;
CURSOR c_period_status(p_trx_date DATE) IS
SELECT (SELECT COUNT(p.status)
FROM ORG_ACCT_PERIODS_V p
WHERE p.organization_id = p_org_id
AND upper(p.status) = upper('Open')
AND trunc(p_trx_date) BETWEEN p.start_date AND p.end_date) inv_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 201
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) pur_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 200
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ap_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 222
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ar_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 101
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) gl_period
FROM dual;
CURSOR c_cut_wip IS
SELECT bcw.c_transaction_id,
bcw.transaction_type,
bcw.transaction_date,
bcw.organization_id,
mp.organization_code,
bcw.transaction_qty,
bcw.transaction_uom,
msi.inventory_item_id,
bcw.item_number,
we.wip_entity_id,
bcw.job_number,
we.entity_type,
wdj.bom_revision_date,
wdj.routing_revision_date,
bcw.reason_name,
bcw.Reference,
wo.operation_seq_num fr_oper_seq_num,
bso.operation_code fr_operation_code,
wo.department_id fr_dept_id,
bd.department_code fr_dept_code,
--'Queue' FM_INTRAOPERATION_STEP_TYPE,
1 FM_INTRAOPERATION_STEP_TYPE,
wo.operation_seq_num to_oper_seq_num,
bso.operation_code to_operation_code,
wo.department_id to_dept_id,
bd.department_code to_dept_code,
--'To move' TO_INTRAOPERATION_STEP_TYPE,
3 TO_INTRAOPERATION_STEP_TYPE,
wdj.completion_subinventory
FROM mtl_parameters mp,
BOM_STANDARD_OPERATIONS bso,
BOM_DEPARTMENTS bd,
WIP_OPERATIONS wo,
mtl_system_items_b msi,
wip_discrete_jobs wdj,
wip_entities we,
BG_CUT_WIP_Interface bcw
WHERE 1 = 1
AND mp.organization_id = p_org_id
AND wo.standard_operation_id = bso.standard_operation_id
AND BD.DEPARTMENT_ID(+) = WO.DEPARTMENT_ID
AND wo.wip_entity_id = we.wip_entity_id
AND msi.inventory_item_id = we.primary_item_id
AND msi.organization_id = we.organization_id
AND wdj.wip_entity_id = we.wip_entity_id
AND we.wip_entity_name = bcw.job_number
AND bcw.transaction_type = 'WIP'
AND bcw.processing_status = 'P'
AND bcw.organization_id = p_org_id;
BEGIN
FOR rec IN c_cut_wip LOOP
OPEN c_period_status(rec.transaction_date);
FETCH c_period_status
INTO v_inv_period,
v_pur_period,
v_ap_period,
v_ar_period,
v_gl_period;
CLOSE c_period_status;
/* INSERT INTO mtl_transactions_interface
( source_code,
source_line_id,
source_header_id,
process_flag,
--validation_required,
transaction_mode,
lock_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
LAST_UPDATE_LOGIN,
inventory_item_id,
organization_id,
transaction_quantity,
primary_quantity,
transaction_uom,
transaction_date,
subinventory_code,
transaction_source_id,
transaction_source_type_id,
transaction_action_id,
transaction_type_id,
transaction_reference,
wip_entity_type,
operation_seq_num,
bom_revision_date,
routing_revision_date,
scheduled_flag,
final_completion_flag,
Flow_schedule,
--revision,
transaction_interface_id)
VALUES('WIP Comp', -- source_code
1, -- source_line_id
1, -- source_header_id
1, -- process_flag
3, -- transaction_mode
2, -- lock_flag
SYSDATE,
fnd_global.user_id, --last_updated_by
SYSDATE,
fnd_global.user_id, -- created_by
-1, --LAST_UPDATE_LOGIN
rec.inventory_item_id, -- inventory_item_id
rec.organization_id,
rec.transaction_qty,
rec.transaction_qty, -- primary_qty
rec.transaction_uom,
SYSDATE,
-- rec.transaction_date,
rec.completion_subinventory,
rec.wip_entity_id, -- transaction_source_id
5, -- trans_source_type_id
31, -- trans_action_id
44, -- trans_type_id
rec.reference||'-001',
rec.entity_type, -- wip_entity_type
rec.fr_oper_seq_num, -- operation_seq_num
NULL, -- rec.bom_revision_date, -- bom_revision_date
NULL, -- rec.routing_revision_date, --routing_revision_date
NULL, -- scheduled_flag
'Y', --final_completion_flag
NULL, --Flow_schedule
NULL ); */
IF v_inv_period = 1 AND v_pur_period = 1 AND v_ap_period = 1 AND
v_ar_period = 1 AND v_gl_period = 1 THEN
INSERT INTO wip_move_txn_interface
(LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATED_BY_NAME,
CREATION_DATE,
CREATED_BY,
CREATED_BY_NAME,
LAST_UPDATE_LOGIN,
group_id,
source_code,
--source_code, source_line_id
PROCESS_PHASE,
PROCESS_STATUS,
transaction_type,
ENTITY_TYPE,
organization_id,
organization_code,
wip_entity_id,
wip_entity_name,
PRIMARY_ITEM_ID,
transaction_date,
--acct_period_id,
fm_operation_seq_num,
fm_operation_code,
fm_department_id,
fm_intraoperation_step_type,
to_operation_seq_num,
to_operation_code,
to_department_id,
to_intraoperation_step_type,
transaction_quantity,
PRIMARY_QUANTITY,
transaction_uom,
REASON_NAME,
reference)
VALUES
(SYSDATE, -- last_update_date
fnd_global.user_id,
fnd_global.user_name, --LAST_UPDATED_BY_NAME
SYSDATE,
fnd_global.user_id,
fnd_global.user_name, --CREATED_BY_NAME
-1, --LAST_UPDATE_LOGIN
NULL, --group_id
'WIP Comp', ---source_code
1, -- process_phase
1, -- process_status
2, -- transaction_type
1, -- entity_type
rec.organization_id,
rec.Organization_Code, -- must be not null ??
rec.wip_entity_id,
rec.job_number, -- wip_entity_name,
rec.Inventory_Item_Id,
rec.transaction_date,
rec.fr_oper_seq_num,
rec.fr_operation_code,
rec.fr_dept_id, -- from_department_id
rec.fm_intraoperation_step_type,
rec.To_Oper_Seq_Num,
rec.To_Operation_Code, -- to_operation_code
rec.To_Dept_Id,
rec.To_Intraoperation_Step_Type,
rec.transaction_qty,
rec.transaction_qty,
rec.transaction_uom,
rec.reason_name,
rec.reference);
UPDATE BG_CUT_WIP_Interface
SET PROCESSING_STATUS = 'S'
WHERE c_transaction_id = rec.c_transaction_id;
ELSE
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'*** The period is not opened! *** Period: ' ||
to_char(rec.transaction_date, 'MON-YYYY'));
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
END IF;
END LOOP;
COMMIT;
--FND_FILE.put_line(1, 'User id : ' || fnd_global.user_id);
END bg_wip_sysauto;
PROCEDURE bg_wip_sysauto(p_org_id IN NUMBER) IS
v_inv_period NUMBER;
v_pur_period NUMBER;
v_ap_period NUMBER;
v_ar_period NUMBER;
v_gl_period NUMBER;
CURSOR c_period_status(p_trx_date DATE) IS
SELECT (SELECT COUNT(p.status)
FROM ORG_ACCT_PERIODS_V p
WHERE p.organization_id = p_org_id
AND upper(p.status) = upper('Open')
AND trunc(p_trx_date) BETWEEN p.start_date AND p.end_date) inv_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 201
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) pur_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 200
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ap_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 222
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ar_period,
(SELECT COUNT(g.closing_status)
FROM GL_PERIOD_STATUSES g
WHERE g.application_id = 101
AND g.set_of_books_id = 302
AND upper(g.closing_status) = upper('O')
AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) gl_period
FROM dual;
CURSOR c_cut_wip IS
SELECT bcw.c_transaction_id,
bcw.transaction_type,
bcw.transaction_date,
bcw.organization_id,
mp.organization_code,
bcw.transaction_qty,
bcw.transaction_uom,
msi.inventory_item_id,
bcw.item_number,
we.wip_entity_id,
bcw.job_number,
we.entity_type,
wdj.bom_revision_date,
wdj.routing_revision_date,
bcw.reason_name,
bcw.Reference,
wo.operation_seq_num fr_oper_seq_num,
bso.operation_code fr_operation_code,
wo.department_id fr_dept_id,
bd.department_code fr_dept_code,
--'Queue' FM_INTRAOPERATION_STEP_TYPE,
1 FM_INTRAOPERATION_STEP_TYPE,
wo.operation_seq_num to_oper_seq_num,
bso.operation_code to_operation_code,
wo.department_id to_dept_id,
bd.department_code to_dept_code,
--'To move' TO_INTRAOPERATION_STEP_TYPE,
3 TO_INTRAOPERATION_STEP_TYPE,
wdj.completion_subinventory
FROM mtl_parameters mp,
BOM_STANDARD_OPERATIONS bso,
BOM_DEPARTMENTS bd,
WIP_OPERATIONS wo,
mtl_system_items_b msi,
wip_discrete_jobs wdj,
wip_entities we,
BG_CUT_WIP_Interface bcw
WHERE 1 = 1
AND mp.organization_id = p_org_id
AND wo.standard_operation_id = bso.standard_operation_id
AND BD.DEPARTMENT_ID(+) = WO.DEPARTMENT_ID
AND wo.wip_entity_id = we.wip_entity_id
AND msi.inventory_item_id = we.primary_item_id
AND msi.organization_id = we.organization_id
AND wdj.wip_entity_id = we.wip_entity_id
AND we.wip_entity_name = bcw.job_number
AND bcw.transaction_type = 'WIP'
AND bcw.processing_status = 'P'
AND bcw.organization_id = p_org_id;
BEGIN
FOR rec IN c_cut_wip LOOP
OPEN c_period_status(rec.transaction_date);
FETCH c_period_status
INTO v_inv_period,
v_pur_period,
v_ap_period,
v_ar_period,
v_gl_period;
CLOSE c_period_status;
/* INSERT INTO mtl_transactions_interface
( source_code,
source_line_id,
source_header_id,
process_flag,
--validation_required,
transaction_mode,
lock_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
LAST_UPDATE_LOGIN,
inventory_item_id,
organization_id,
transaction_quantity,
primary_quantity,
transaction_uom,
transaction_date,
subinventory_code,
transaction_source_id,
transaction_source_type_id,
transaction_action_id,
transaction_type_id,
transaction_reference,
wip_entity_type,
operation_seq_num,
bom_revision_date,
routing_revision_date,
scheduled_flag,
final_completion_flag,
Flow_schedule,
--revision,
transaction_interface_id)
VALUES('WIP Comp', -- source_code
1, -- source_line_id
1, -- source_header_id
1, -- process_flag
3, -- transaction_mode
2, -- lock_flag
SYSDATE,
fnd_global.user_id, --last_updated_by
SYSDATE,
fnd_global.user_id, -- created_by
-1, --LAST_UPDATE_LOGIN
rec.inventory_item_id, -- inventory_item_id
rec.organization_id,
rec.transaction_qty,
rec.transaction_qty, -- primary_qty
rec.transaction_uom,
SYSDATE,
-- rec.transaction_date,
rec.completion_subinventory,
rec.wip_entity_id, -- transaction_source_id
5, -- trans_source_type_id
31, -- trans_action_id
44, -- trans_type_id
rec.reference||'-001',
rec.entity_type, -- wip_entity_type
rec.fr_oper_seq_num, -- operation_seq_num
NULL, -- rec.bom_revision_date, -- bom_revision_date
NULL, -- rec.routing_revision_date, --routing_revision_date
NULL, -- scheduled_flag
'Y', --final_completion_flag
NULL, --Flow_schedule
NULL ); */
IF v_inv_period = 1 AND v_pur_period = 1 AND v_ap_period = 1 AND
v_ar_period = 1 AND v_gl_period = 1 THEN
INSERT INTO wip_move_txn_interface
(LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATED_BY_NAME,
CREATION_DATE,
CREATED_BY,
CREATED_BY_NAME,
LAST_UPDATE_LOGIN,
group_id,
source_code,
--source_code, source_line_id
PROCESS_PHASE,
PROCESS_STATUS,
transaction_type,
ENTITY_TYPE,
organization_id,
organization_code,
wip_entity_id,
wip_entity_name,
PRIMARY_ITEM_ID,
transaction_date,
--acct_period_id,
fm_operation_seq_num,
fm_operation_code,
fm_department_id,
fm_intraoperation_step_type,
to_operation_seq_num,
to_operation_code,
to_department_id,
to_intraoperation_step_type,
transaction_quantity,
PRIMARY_QUANTITY,
transaction_uom,
REASON_NAME,
reference)
VALUES
(SYSDATE, -- last_update_date
fnd_global.user_id,
fnd_global.user_name, --LAST_UPDATED_BY_NAME
SYSDATE,
fnd_global.user_id,
fnd_global.user_name, --CREATED_BY_NAME
-1, --LAST_UPDATE_LOGIN
NULL, --group_id
'WIP Comp', ---source_code
1, -- process_phase
1, -- process_status
2, -- transaction_type
1, -- entity_type
rec.organization_id,
rec.Organization_Code, -- must be not null ??
rec.wip_entity_id,
rec.job_number, -- wip_entity_name,
rec.Inventory_Item_Id,
rec.transaction_date,
rec.fr_oper_seq_num,
rec.fr_operation_code,
rec.fr_dept_id, -- from_department_id
rec.fm_intraoperation_step_type,
rec.To_Oper_Seq_Num,
rec.To_Operation_Code, -- to_operation_code
rec.To_Dept_Id,
rec.To_Intraoperation_Step_Type,
rec.transaction_qty,
rec.transaction_qty,
rec.transaction_uom,
rec.reason_name,
rec.reference);
UPDATE BG_CUT_WIP_Interface
SET PROCESSING_STATUS = 'S'
WHERE c_transaction_id = rec.c_transaction_id;
ELSE
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'*** The period is not opened! *** Period: ' ||
to_char(rec.transaction_date, 'MON-YYYY'));
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'------------------------------------------');
END IF;
END LOOP;
COMMIT;
--FND_FILE.put_line(1, 'User id : ' || fnd_global.user_id);
END bg_wip_sysauto;
FUNCTION bg_get_receipt_qty(p_in_po_header_id IN NUMBER,
p_in_po_line_number IN NUMBER,
p_in_shipment_no IN NUMBER,
p_in_waybill IN VARCHAR2) RETURN NUMBER IS
v_receipt_qty NUMBER;
v_po_number po_headers_all.Segment1%TYPE;
CURSOR c_get_po IS
SELECT segment1
FROM po_headers_all
WHERE po_header_id = p_in_po_header_id;
CURSOR c_get_qty IS
SELECT bcr.transaction_qty
FROM BG_CUT_RCV_Interface bcr
WHERE bcr.waybill = p_in_waybill
AND bcr.line_number = p_in_po_line_number
AND bcr.shipment_line_num = p_in_shipment_no
AND bcr.transaction_type = 'PO'
AND bcr.processing_status = 'P'
AND bcr.po_number = v_po_number;
BEGIN
OPEN c_get_po;
FETCH c_get_po
INTO v_po_number;
CLOSE c_get_po;
OPEN c_get_qty;
FETCH c_get_qty
INTO v_receipt_qty;
CLOSE c_get_qty;
RETURN v_receipt_qty;
END bg_get_receipt_qty;
p_in_po_line_number IN NUMBER,
p_in_shipment_no IN NUMBER,
p_in_waybill IN VARCHAR2) RETURN NUMBER IS
v_receipt_qty NUMBER;
v_po_number po_headers_all.Segment1%TYPE;
CURSOR c_get_po IS
SELECT segment1
FROM po_headers_all
WHERE po_header_id = p_in_po_header_id;
CURSOR c_get_qty IS
SELECT bcr.transaction_qty
FROM BG_CUT_RCV_Interface bcr
WHERE bcr.waybill = p_in_waybill
AND bcr.line_number = p_in_po_line_number
AND bcr.shipment_line_num = p_in_shipment_no
AND bcr.transaction_type = 'PO'
AND bcr.processing_status = 'P'
AND bcr.po_number = v_po_number;
BEGIN
OPEN c_get_po;
FETCH c_get_po
INTO v_po_number;
CLOSE c_get_po;
OPEN c_get_qty;
FETCH c_get_qty
INTO v_receipt_qty;
CLOSE c_get_qty;
RETURN v_receipt_qty;
END bg_get_receipt_qty;
FUNCTION bg_get_conv_rate(p_in_po_number IN VARCHAR2,
p_in_org_id IN NUMBER,
p_in_trx_date IN DATE) RETURN NUMBER IS
v_currency_code po_headers_all.currency_code%TYPE;
v_rate gl_daily_rates.conversion_rate%TYPE;
CURSOR c_get_currency_code IS
SELECT poh.currency_code
FROM po_headers_all poh
WHERE poh.org_id = p_in_org_id
AND poh.segment1 = p_in_po_number;
BEGIN
BEGIN
OPEN c_get_currency_code;
FETCH c_get_currency_code
INTO v_currency_code;
CLOSE c_get_currency_code;
IF v_currency_code = 'CNY' THEN
v_rate := 1;
ELSE
SELECT conversion_rate
INTO v_rate
FROM GL_DAILY_RATES
WHERE from_currency = v_currency_code
AND to_currency = 'CNY'
AND conversion_date = trunc(p_in_trx_date)
AND conversion_type = 'Corporate';
END IF;
EXCEPTION
WHEN OTHERS THEN
v_rate := NULL;
END;
RETURN v_rate;
END bg_get_conv_rate;
p_in_org_id IN NUMBER,
p_in_trx_date IN DATE) RETURN NUMBER IS
v_currency_code po_headers_all.currency_code%TYPE;
v_rate gl_daily_rates.conversion_rate%TYPE;
CURSOR c_get_currency_code IS
SELECT poh.currency_code
FROM po_headers_all poh
WHERE poh.org_id = p_in_org_id
AND poh.segment1 = p_in_po_number;
BEGIN
BEGIN
OPEN c_get_currency_code;
FETCH c_get_currency_code
INTO v_currency_code;
CLOSE c_get_currency_code;
IF v_currency_code = 'CNY' THEN
v_rate := 1;
ELSE
SELECT conversion_rate
INTO v_rate
FROM GL_DAILY_RATES
WHERE from_currency = v_currency_code
AND to_currency = 'CNY'
AND conversion_date = trunc(p_in_trx_date)
AND conversion_type = 'Corporate';
END IF;
EXCEPTION
WHEN OTHERS THEN
v_rate := NULL;
END;
RETURN v_rate;
END bg_get_conv_rate;
FUNCTION bg_get_employee_name(p_in_user_id IN NUMBER) RETURN VARCHAR2 IS
v_employee_name VARCHAR2(100);
CURSOR c_get_name IS
SELECT full_name
FROM PER_ALL_PEOPLE_F p, fnd_user f
WHERE f.employee_id = p.person_id
AND SYSDATE BETWEEN p.effective_start_date AND
p.effective_end_date
AND f.user_id = p_in_user_id;
BEGIN
OPEN c_get_name;
FETCH c_get_name
INTO v_employee_name;
CLOSE c_get_name;
RETURN v_employee_name;
END bg_get_employee_name;
v_employee_name VARCHAR2(100);
CURSOR c_get_name IS
SELECT full_name
FROM PER_ALL_PEOPLE_F p, fnd_user f
WHERE f.employee_id = p.person_id
AND SYSDATE BETWEEN p.effective_start_date AND
p.effective_end_date
AND f.user_id = p_in_user_id;
BEGIN
OPEN c_get_name;
FETCH c_get_name
INTO v_employee_name;
CLOSE c_get_name;
RETURN v_employee_name;
END bg_get_employee_name;
END INVGRCVK_BG_1;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9182041/viewspace-732781/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9182041/viewspace-732781/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
