[Home] [Help]
PACKAGE: APPS.GMP_APS_DS_PULL
Source
1 PACKAGE gmp_aps_ds_pull AUTHID CURRENT_USER AS
2 /* $Header: GMPPLDSS.pls 120.4.12020000.1 2012/06/27 15:07:53 appldev ship $ */
3
4 /* Global value variables */
5 v_sql_stmt VARCHAR2(4000) := NULL;
6 v_item_sql_stmt VARCHAR2(4000) := NULL;
7 v_sales_sql_stmt VARCHAR2(4000) := NULL;
8 v_forecast_sql_stmt VARCHAR2(4000) := NULL;
9 v_association_sql_stmt VARCHAR2(4000) := NULL;
10 v_null_date DATE := TO_DATE('01/01/1970','DD/MM/YYYY');
11
12 /* Variables for document types */
13 v_doc_prod VARCHAR2(4) := 'PROD';
14 v_doc_fpo VARCHAR2(4) := 'FPO';
15 v_doc_opso VARCHAR2(4) := 'OPSO';
16 v_cp_enabled BOOLEAN := FALSE;
17
18 /* Procedure to extract production order to the for demands and supplies */
19 PROCEDURE production_orders(
20 pdblink IN VARCHAR2,
21 pinstance_id IN NUMBER,
22 prun_date IN DATE,
23 pdelimiter IN VARCHAR2,
24 return_status IN OUT NOCOPY BOOLEAN);
25
26 /* Universal routine to write to msc_st_supplies table */
27 PROCEDURE insert_supplies(
28 pitem_id PLS_INTEGER,
29 porganization_id PLS_INTEGER,
30 pinstance_id PLS_INTEGER,
31 pdate DATE,
32 pstart_date DATE,
33 pend_date DATE,
34 pbatch_id PLS_INTEGER,
35 pqty NUMBER,
36 pfirmed_ind NUMBER,
37 pbatchstep_no NUMBER, /* B2919303 */
38 porder_no VARCHAR2,
39 plot_number VARCHAR2,
40 pexpire_date DATE,
41 psupply_type NUMBER,
42 pproduct_item_id PLS_INTEGER); /* B2953953 - CoProduct changes */
43
44 /* Universal routine to write to msc_st_demands table */
45 PROCEDURE insert_demands(
46 pitem_id PLS_INTEGER,
47 porganization_id PLS_INTEGER,
48 pinstance_id PLS_INTEGER,
49 pbatch_id PLS_INTEGER,
50 pproduct_item_id PLS_INTEGER,
51 pdate DATE,
52 pqty NUMBER,
53 pbatchstep_no NUMBER, /* B2919303 */
54 porder_no VARCHAR2,
55 pdesignator VARCHAR2,
56 pnet_price NUMBER, /* B1200400 */
57 porigination_type NUMBER,
58 api_mode BOOLEAN DEFAULT FALSE,
59 pschedule_id NUMBER DEFAULT NULL);
60
61 /* routine to write to msc_st_resource_requirements */
62 PROCEDURE insert_resource_requirements(
63 porganization_id PLS_INTEGER,
64 pinstance_id PLS_INTEGER,
65 pseq_num PLS_INTEGER,
66 presource_id PLS_INTEGER,
67 pstart_date DATE,
68 pend_date DATE,
69 presource_usage NUMBER,
70 prsrc_cnt NUMBER,
71 pbatchstep_no NUMBER, /* B1224660 added new parameter */
72 pbatch_id PLS_INTEGER,
73 pstep_status NUMBER,
74 pschedule_flag NUMBER,
75 pparent_seq_num NUMBER,
76 pmin_xfer_qty NUMBER);
77
78 /* Procedure to extract onhand balances */
79 PROCEDURE extract_onhand_balances(
80 pdblink IN VARCHAR2,
81 pinstance_id IN PLS_INTEGER,
82 prun_date IN DATE,
83 pdelimiter IN VARCHAR2,
84 return_status IN OUT NOCOPY BOOLEAN);
85
86 /* Procedure to extract Inventory Transfers Demands B2756431 */
87 PROCEDURE extract_inv_transfer_demands(
88 pdblink IN VARCHAR2,
89 pinstance_id IN PLS_INTEGER,
90 prun_date IN DATE,
91 pdelimiter IN VARCHAR2,
92 pwhse_code IN VARCHAR2,
93 pdesignator IN VARCHAR2,
94 return_status IN OUT NOCOPY BOOLEAN);
95
96 /* Procedure to extract Inventory Transfers Supplies B2756431 */
97 PROCEDURE extract_inv_transfer_supplies(
98 pdblink IN VARCHAR2,
99 pinstance_id IN PLS_INTEGER,
100 prun_date IN DATE,
101 pdelimiter IN VARCHAR2,
102 return_status IN OUT NOCOPY BOOLEAN);
103
104
105 PROCEDURE onhand_inventory(
106 pdblink IN VARCHAR2,
107 pinstance_id IN PLS_INTEGER,
108 prun_date IN DATE,
109 pdelimiter IN VARCHAR2,
110 return_status IN OUT NOCOPY BOOLEAN);
111
112 /* Procedure to develop designator names */
113 PROCEDURE build_designator(
114 poccur IN NUMBER,
115 pdelimiter IN VARCHAR2,
116 pdesignator OUT NOCOPY VARCHAR2);
117
118 PROCEDURE sales_forecast_api(
119 errbuf OUT NOCOPY VARCHAR2,
120 retcode OUT NOCOPY VARCHAR2,
121 p_cp_enabled IN BOOLEAN DEFAULT TRUE,
122 p_run_date IN DATE DEFAULT SYSDATE);
123
124 /* Procedure to extract the sales and forecast demands */
125 PROCEDURE sales_forecast( pdblink IN VARCHAR2,
126 pinstance_id IN PLS_INTEGER,
127 prun_date IN DATE,
128 pdelimiter IN VARCHAR2,
129 return_status IN OUT NOCOPY BOOLEAN,
130 api_mode IN BOOLEAN DEFAULT FALSE);
131
132 PROCEDURE write_this_so(pcounter IN NUMBER,
133 sapi_mode IN BOOLEAN DEFAULT FALSE) ;
134
135 PROCEDURE write_this_fcst(pcounter IN NUMBER,
136 fapi_mode IN BOOLEAN DEFAULT FALSE) ;
137
138 FUNCTION associate_forecasts ( pschd_fcst_cnt IN NUMBER,
139 pschd_id IN PLS_INTEGER ) return BOOLEAN ;
140
141 FUNCTION check_so( pso_counter IN NUMBER,
142 pinventory_item_id IN PLS_INTEGER,
143 porganization_id IN PLS_INTEGER) return BOOLEAN ;
144
145 FUNCTION check_forecast(pfcst_counter IN NUMBER,
146 pinventory_item_id IN PLS_INTEGER,
147 porganization_id IN PLS_INTEGER) return BOOLEAN ;
148
149 PROCEDURE consume_forecast( pinventory_item_id IN PLS_INTEGER,
150 porganization_id IN PLS_INTEGER,
151 papi_mode IN BOOLEAN DEFAULT FALSE) ;
152
153 PROCEDURE write_forecast( pfcst_counter IN NUMBER,
154 pinventory_item_id IN PLS_INTEGER,
155 porganization_id IN PLS_INTEGER,
156 papi_mode IN BOOLEAN DEFAULT FALSE) ;
157
158 PROCEDURE write_so( pso_counter IN NUMBER,
159 pinventory_item_id IN PLS_INTEGER,
160 porganization_id IN PLS_INTEGER,
161 papi_mode IN BOOLEAN DEFAULT FALSE) ;
162
163 PROCEDURE time_stamp ;
164
165 PROCEDURE insert_designator ;
166
167 PROCEDURE process_resource_rows(
168 pfirst_row IN NUMBER,
169 plast_row IN NUMBER,
170 pfound_mtl IN NUMBER,
171 porgn_id IN PLS_INTEGER,
172 pinstance_id IN PLS_INTEGER,
173 pinflate_wip IN NUMBER,
174 pmin_xfer_qty IN NUMBER);
175
176 PROCEDURE LOG_MESSAGE(pBUFF IN VARCHAR2) ;
177 PROCEDURE extract_forecasts( pdblink IN VARCHAR2,
178 pinstance_id IN PLS_INTEGER,
179 prun_date IN DATE,
180 pdelimiter IN VARCHAR2,
181 return_status IN OUT NOCOPY BOOLEAN);
182
183 /*Sowmya - As Per latest FDD changes - Start*/
184 PROCEDURE production_reservations ( pdblink IN VARCHAR2,
185 pinstance_id IN PLS_INTEGER,
186 prun_date IN DATE,
187 pdelimiter IN VARCHAR2,
188 return_status IN OUT NOCOPY BOOLEAN);
189 /*Sowmya - As Per latest FDD changes - End*/
190
191 /*Sowmya */
192 PROCEDURE update_last_setup_id (
193 effbuf OUT NOCOPY VARCHAR2,
194 retcode OUT NOCOPY NUMBER,
195 f_orgn_code IN NUMBER,
196 t_orgn_code IN NUMBER
197 );
198 /*Sowmya */
199
200 PROCEDURE gmp_debug_message(pBUFF IN VARCHAR2); -- Bug: 8420747 Vpedarla
201
202 END gmp_aps_ds_pull;