[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_PRE_R12_COGS_PKG
Source
1 PACKAGE BODY opi_dbi_pre_r12_cogs_pkg AS
2 /* $Header: OPIDECOGSPB.pls 120.2 2007/03/15 07:38:58 kvelucha ship $ */
3
4
5 /*=========================================
6 Package Level Constants
7 ==========================================*/
8
9 -- Source constants
10 PRE_R12_OPM_SOURCE CONSTANT NUMBER := 3;
11
12 -- Flag for Inventory Turns
13 INCLUDE_FOR_TURNS CONSTANT NUMBER := 1;
14 DO_NOT_INCLUDE_FOR_TURNS CONSTANT NUMBER := 2;
15
16
17 /*=================================================================
18 This procedure extracts process data from the Pre R12 data model
19 into the staging table. It is only called from the R12 COGS
20 package when the global start date is before the R12 migration date.
21
22 Parameters:
23 - p_global_start_date: global start date
24 - errbuf: error buffer
25 - retcode: return code
26 ===================================================================*/
27
28 PROCEDURE pre_r12_opm_cogs( p_global_start_date IN DATE,
29 errbuf IN OUT NOCOPY VARCHAR2,
30 retcode IN OUT NOCOPY NUMBER) IS
31 -- Declaration block
32 l_stmt number;
33
34 BEGIN
35
36 -- Initialization block
37 l_stmt := 0;
38
39 bis_collection_utilities.put_line('Enter pre_r12_opm_cogs() ' ||
40 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
41 INSERT /*+ append parallel(opi_dbi_cogs_fstg) */ into opi_dbi_cogs_fstg (
42 inventory_item_id,
43 organization_id,
44 order_line_id,
45 top_model_line_id,
46 top_model_item_id,
47 top_model_item_uom,
48 top_model_org_id,
49 customer_id,
50 cogs_val_b_draft,
51 cogs_val_b,
52 cogs_date,
53 source,
54 turns_cogs_flag,
55 internal_flag
56 )
57 (SELECT
58 inventory_item_id,
59 organization_id,
60 order_line_id,
61 order_line_id,
62 inventory_item_id,
63 top_model_item_uom,
64 organization_id,
65 sold_to_org_id,
66 0,
67 sum(COGS_VAL_B),
68 max(COGS_DATE),
69 source,
70 turns_cogs_flag,
71 internal_flag
72 FROM
73 (SELECT /*+ full(led) use_hash(led, lines,cust_acct,msi,whse) parallel(led) parallel(lines) parallel(cust_acct) parallel(msi) parallel(whse) */
74 lines.inventory_item_id inventory_item_id,
75 whse.mtl_organization_id organization_id,
76 tran.oe_order_line_id order_line_id,
77 msi.primary_uom_code top_model_item_uom,
78 0 cogs_val_b_draft,
79 led.debit_credit_sign*led.amount_base cogs_val_b,
80 trunc(gl_trans_date) cogs_date,
81 nvl(cust_acct.party_id, -1) sold_to_org_id,
82 Decode(lines.source_type_code,
83 'EXTERNAL', DO_NOT_INCLUDE_FOR_TURNS,
84 INCLUDE_FOR_TURNS) turns_cogs_flag,
85 Decode(lines.order_source_id, 10, 1, 0) INTERNAL_FLAG,
86 PRE_R12_OPM_SOURCE source
87 FROM gl_subr_led led,
88 (SELECT /*+ full(tran) full(rcv) use_hash(tran) parallel(tran) parallel(rcv) */
89 tran.doc_type,
90 rcv.oe_order_line_id oe_order_line_id,
91 tran.line_id,
92 tran.orgn_code,
93 tran.whse_code
94 FROM ic_tran_pnd tran,
95 rcv_transactions rcv
96 WHERE doc_type = 'PORC'
97 AND completed_ind = 1
98 AND gl_posted_ind = 1
99 AND tran.line_id = rcv.transaction_id
100 AND rcv.oe_order_line_id is NOT NULL
101 GROUP BY doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
102 UNION ALL
103 SELECT /*+ full(tran) parallel(tran) */
104 tran.doc_type,
105 tran.line_id oe_order_line_id,
106 tran.line_id,
107 tran.orgn_code,
108 tran.whse_code
109 FROM ic_tran_pnd tran
110 WHERE doc_type = 'OMSO'
111 AND completed_ind = 1
112 AND gl_posted_ind = 1
113 GROUP BY doc_type, line_id, line_id, orgn_code, whse_code) tran,
114 oe_order_lines_all lines,
115 hz_cust_accounts cust_acct,
116 mtl_system_items_b msi,
117 ic_whse_mst whse
118 WHERE led.doc_type in ( 'OMSO', 'PORC' )
119 AND led.acct_ttl_type = 5200
120 AND lines.line_id = tran.oe_order_line_id
121 AND lines.sold_to_org_id = cust_acct.cust_account_id(+)
122 AND tran.doc_type = led.doc_type
123 AND tran.line_id = led.line_id
124 AND whse.whse_code = tran.whse_code
125 AND msi.inventory_item_id=lines.inventory_item_id
126 AND msi.organization_id=lines.ship_from_org_id
127 AND led.GL_TRANS_DATE >= p_global_start_date )
128 GROUP BY inventory_item_id,
129 organization_id,
130 top_model_item_uom,
131 sold_to_org_id,
132 order_line_id,
133 turns_cogs_flag,
134 internal_flag,
135 source );
136
137 l_stmt := 1;
138
139 COMMIT;
140
141 bis_collection_utilities.put_line('Exit pre_r12_opm_cogs() ' ||
142 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
143
144 EXCEPTION WHEN OTHERS THEN
145 --{
146 ROLLBACK;
147
148 bis_collection_utilities.put_line(' Error in pre_r12_opm_cogs() at statement');
149 bis_collection_utilities.put_line( Sqlerrm );
150 --}
151 END pre_r12_opm_cogs;
152
153 END opi_dbi_pre_r12_cogs_pkg;