eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.DDR_BASE_UTIL_PKG

Source


1 PACKAGE BODY ddr_base_util_pkg AS
2 /* $Header: ddrubasb.pls 120.1 2008/02/17 07:46:18 vbhave noship $ */
3 
4 FUNCTION RTL_INV_ITEM_DUPS_FNC RETURN VARCHAR2
5 AS
6 BEGIN
7 
8 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
9 
10 INSERT INTO DDR_E_RTL_INV_ITEM
11     (REC_ID, LOAD_ID, ERR_REASON,
12     GLBL_ITEM_ID, GLBL_ITEM_ID_TYP,RTL_BSNS_UNIT_CD,
13     RTL_SKU_ITEM_NBR,INV_LOC_CD,UOM, ON_HAND_QTY,RECVD_QTY,
14     IN_TRANSIT_QTY,BCK_ORDR_QTY,QLTY_HOLD_QTY,
15     ON_HAND_NET_COST_AMT, RECVD_NET_COST_AMT,
16     IN_TRANSIT_NET_COST_AMT, BCKORDR_NET_COST_AMT,
17     QLTY_HOLD_NET_COST_AMT, ON_HAND_RTL_AMT, RECVD_RTL_AMT,
18     IN_TRANSIT_RTL_AMT,BCKORDR_RTL_AMT,
19     QLTY_HOLD_RTL_AMT,SRC_SYS_IDNT, SRC_SYS_DT,
20     SRC_IDNT_FLAG, ACTION_FLAG, TRANS_DT
21      )
22     SELECT REC_ID, DDR_LOAD_SEQ.NEXTVAL,'Duplicate Record',
23     GLBL_ITEM_ID,GLBL_ITEM_ID_TYP,  RTL_BSNS_UNIT_CD,
24     RTL_SKU_ITEM_NBR, INV_LOC_CD,UOM,
25     ON_HAND_QTY, RECVD_QTY,
26     IN_TRANSIT_QTY, BCK_ORDR_QTY,
27     QLTY_HOLD_QTY, ON_HAND_NET_COST_AMT,
28     RECVD_NET_COST_AMT, IN_TRANSIT_NET_COST_AMT,
29     BCKORDR_NET_COST_AMT, QLTY_HOLD_NET_COST_AMT,
30     ON_HAND_RTL_AMT, RECVD_RTL_AMT,
31     IN_TRANSIT_RTL_AMT, BCKORDR_RTL_AMT,
32     QLTY_HOLD_RTL_AMT, SRC_SYS_IDNT,
33     SRC_SYS_DT,'I','N',TRANS_DT
34       FROM DDR_I_RTL_INV_ITEM
35        WHERE
36            (RTL_BSNS_UNIT_CD,
37           TRANS_DT,
38           GLBL_ITEM_ID,
39           RTL_SKU_ITEM_NBR,
40           GLBL_ITEM_ID_TYP,
41           INV_LOC_CD)
42           IN (
43              SELECT
44             RTL_BSNS_UNIT_CD,
45           TRANS_DT,
46           GLBL_ITEM_ID,
47           RTL_SKU_ITEM_NBR,
48           GLBL_ITEM_ID_TYP,
49           INV_LOC_CD
50           FROM DDR_I_RTL_INV_ITEM
51           GROUP BY
52               RTL_BSNS_UNIT_CD,
53           TRANS_DT,
54           GLBL_ITEM_ID,
55           RTL_SKU_ITEM_NBR,
56           GLBL_ITEM_ID_TYP,
57           INV_LOC_CD
58              HAVING COUNT(*) > 1
59          );
60 COMMIT;
61 
62 
63 -- DELETE DUPLICATE RECORDS FROM INTERFACE TABLE --
64 
65 
66 DELETE FROM DDR_I_RTL_INV_ITEM
67     WHERE
68       (RTL_BSNS_UNIT_CD,
69    TRANS_DT,
70    GLBL_ITEM_ID,
71    RTL_SKU_ITEM_NBR,
72    GLBL_ITEM_ID_TYP,
73    INV_LOC_CD)
74     IN (
75      SELECT
76        RTL_BSNS_UNIT_CD,
77     TRANS_DT,
78     GLBL_ITEM_ID,
79     RTL_SKU_ITEM_NBR,
80     GLBL_ITEM_ID_TYP,
81     INV_LOC_CD
82     FROM DDR_E_RTL_INV_ITEM
83     GROUP BY
84         RTL_BSNS_UNIT_CD,
85       TRANS_DT,
86       GLBL_ITEM_ID,
87       RTL_SKU_ITEM_NBR,
88       GLBL_ITEM_ID_TYP,
89       INV_LOC_CD
90          HAVING COUNT(*) > 1
91     );
92 COMMIT;
93 
94 RETURN('Y');
95 
96 END RTL_INV_ITEM_DUPS_FNC;
97 
98 FUNCTION PRMTN_PLN_DUPS_FNC RETURN VARCHAR2
99 AS
100 BEGIN
101 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
102 INSERT INTO DDR_E_PRMTN_PLN
103     (REC_ID, LOAD_ID, ERR_REASON,RTL_BSNS_UNIT_CD,
104      PRMTN_TYP,
105   PRMTN_FROM_DT,
106   PRMTN_TO_DT,
107   GLBL_ITEM_ID,
108   RTL_SKU_ITEM_NBR,
109   GLBL_ITEM_ID_TYP,
110   PRMTN_PRICE_AMT,
111   SRC_SYS_IDNT,
112   SRC_SYS_DT,
113   SRC_IDNT_FLAG,
114   ACTION_FLAG,
115   TRANS_DT
116      )
117     SELECT REC_ID, DDR_LOAD_SEQ.NEXTVAL,'Duplicate Record',
118     RTL_BSNS_UNIT_CD,
119     PRMTN_TYP,
120     PRMTN_FROM_DT,
121     PRMTN_TO_DT,
122     GLBL_ITEM_ID,
123     RTL_SKU_ITEM_NBR,
124     GLBL_ITEM_ID_TYP,
125     PRMTN_PRICE_AMT,
126     SRC_SYS_IDNT,
127     SRC_SYS_DT,
128     'I','N',TRANS_DT
129       FROM DDR_I_PRMTN_PLN
130        WHERE
131            (GLBL_ITEM_ID,
132         RTL_BSNS_UNIT_CD,
133         PRMTN_TYP,
134         PRMTN_FROM_DT,
135         PRMTN_TO_DT,
136         RTL_SKU_ITEM_NBR,
137         GLBL_ITEM_ID_TYP,
138         TRANS_DT)
139           IN (
140              SELECT
141             GLBL_ITEM_ID,
142           RTL_BSNS_UNIT_CD,
143           PRMTN_TYP,
144           PRMTN_FROM_DT,
145           PRMTN_TO_DT,
146           RTL_SKU_ITEM_NBR,
147           GLBL_ITEM_ID_TYP,
148           TRANS_DT
149           FROM DDR_I_PRMTN_PLN
150           GROUP BY
151               GLBL_ITEM_ID,
152           RTL_BSNS_UNIT_CD,
153           PRMTN_TYP,
154           PRMTN_FROM_DT,
155           PRMTN_TO_DT,
156           RTL_SKU_ITEM_NBR,
157           GLBL_ITEM_ID_TYP,
158           TRANS_DT
159              HAVING COUNT(*) > 1
160          );
161 COMMIT;
162 
163 -- DELETE DUPLICATE RECORDS FROM INTERFACE TABLE --
164 
165 
166 DELETE FROM DDR_I_PRMTN_PLN
167     WHERE
168       (GLBL_ITEM_ID,
169    RTL_BSNS_UNIT_CD,
170    PRMTN_TYP,
171    PRMTN_FROM_DT,
172    PRMTN_TO_DT,
173    RTL_SKU_ITEM_NBR,
174    GLBL_ITEM_ID_TYP,
175    TRANS_DT)
176     IN (
177      SELECT
178        GLBL_ITEM_ID,
179     RTL_BSNS_UNIT_CD,
180     PRMTN_TYP,
181     PRMTN_FROM_DT,
182     PRMTN_TO_DT,
183     RTL_SKU_ITEM_NBR,
184     GLBL_ITEM_ID_TYP,
185     TRANS_DT
186     FROM DDR_E_PRMTN_PLN
187     GROUP BY
188         GLBL_ITEM_ID,
189       RTL_BSNS_UNIT_CD,
190       PRMTN_TYP,
191       PRMTN_FROM_DT,
192       PRMTN_TO_DT,
193       RTL_SKU_ITEM_NBR,
194       GLBL_ITEM_ID_TYP,
195       TRANS_DT
196          HAVING COUNT(*) > 1
197     );
198 COMMIT;
199 
200 RETURN('Y');
201 
202 END PRMTN_PLN_DUPS_FNC;
203 
204 FUNCTION RTL_ORDR_ITEM_DUPS_FNC RETURN VARCHAR2
205 AS
206 BEGIN
207 
208 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
209 
210 
211 INSERT INTO DDR_E_RTL_ORDR_ITEM
212     (REC_ID, LOAD_ID, ERR_REASON,
213     RTL_BSNS_UNIT_CD,GLBL_ITEM_ID,
214     RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP, UOM,
215     ORDR_QTY,ORDR_AMT,SRC_SYS_IDNT,
216     SRC_SYS_DT, TRANS_DT, SRC_IDNT_FLAG,
217     ACTION_FLAG
218      )
219     SELECT REC_ID, DDR_LOAD_SEQ.NEXTVAL,'Duplicate Record',
220     RTL_BSNS_UNIT_CD,GLBL_ITEM_ID,
221        RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP, UOM,
222       ORDR_QTY,ORDR_AMT,SRC_SYS_IDNT,
223       SRC_SYS_DT, TRANS_DT,'I','N'
224       FROM DDR_I_RTL_ORDR_ITEM
225        WHERE
226            (RTL_BSNS_UNIT_CD,
227           GLBL_ITEM_ID,
228           RTL_SKU_ITEM_NBR,
229           GLBL_ITEM_ID_TYP,
230           TRANS_DT)
231           IN (
232              SELECT
233             RTL_BSNS_UNIT_CD,
234           GLBL_ITEM_ID,
235           RTL_SKU_ITEM_NBR,
236           GLBL_ITEM_ID_TYP,
237           TRANS_DT
238           FROM DDR_I_RTL_ORDR_ITEM
239           GROUP BY
240               RTL_BSNS_UNIT_CD,
241           GLBL_ITEM_ID,
242           RTL_SKU_ITEM_NBR,
243           GLBL_ITEM_ID_TYP,
244           TRANS_DT
245              HAVING COUNT(*) > 1
246          );
247 COMMIT;
248 
249 
250 -- DELETE DUPLICATE RECORDS FROM INTERFACE TABLE --
251 
252 
253 DELETE FROM DDR_I_RTL_ORDR_ITEM
254     WHERE
255       (RTL_BSNS_UNIT_CD,
256    GLBL_ITEM_ID,
257    RTL_SKU_ITEM_NBR,
258    GLBL_ITEM_ID_TYP,
259    TRANS_DT)
260     IN (
261      SELECT
262        RTL_BSNS_UNIT_CD,
263     GLBL_ITEM_ID,
264     RTL_SKU_ITEM_NBR,
265     GLBL_ITEM_ID_TYP,
266     TRANS_DT
267     FROM DDR_E_RTL_ORDR_ITEM
268     GROUP BY
269         RTL_BSNS_UNIT_CD,
270       GLBL_ITEM_ID,
271       RTL_SKU_ITEM_NBR,
272       GLBL_ITEM_ID_TYP,
273       TRANS_DT
274          HAVING COUNT(*) > 1
275     );
276 COMMIT;
277 
278 RETURN('Y');
279 
280 END RTL_ORDR_ITEM_DUPS_FNC;
281 
282 FUNCTION RTL_SHIP_ITEM_DUPS_FNC RETURN VARCHAR2
283 AS
284 BEGIN
285 
286 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
287 
288 
289 INSERT INTO DDR_E_RTL_SHIP_ITEM
290     (REC_ID, LOAD_ID, ERR_REASON,
291     RTL_BSNS_UNIT_CD,GLBL_ITEM_ID,
292     RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
293     UOM,SHIP_QTY, SHIP_AMT, SRC_SYS_IDNT,
294     SRC_SYS_DT, SRC_IDNT_FLAG, ACTION_FLAG, TRANS_DT
295      )
296     SELECT REC_ID, DDR_LOAD_SEQ.NEXTVAL,'Duplicate Record',
297     RTL_BSNS_UNIT_CD,GLBL_ITEM_ID,
298        RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
299        UOM,SHIP_QTY, SHIP_AMT, SRC_SYS_IDNT,
300        SRC_SYS_DT,'I','N', TRANS_DT
301       FROM DDR_I_RTL_SHIP_ITEM
302        WHERE
303            (GLBL_ITEM_ID,
304           RTL_BSNS_UNIT_CD,
305           RTL_SKU_ITEM_NBR,
306           GLBL_ITEM_ID_TYP,
307           TRANS_DT)
308           IN (
309              SELECT
310             GLBL_ITEM_ID,
311           RTL_BSNS_UNIT_CD,
312           RTL_SKU_ITEM_NBR,
313           GLBL_ITEM_ID_TYP,
314           TRANS_DT
315           FROM DDR_I_RTL_SHIP_ITEM
316           GROUP BY
317               GLBL_ITEM_ID,
318           RTL_BSNS_UNIT_CD,
319           RTL_SKU_ITEM_NBR,
320           GLBL_ITEM_ID_TYP,
321           TRANS_DT
322              HAVING COUNT(*) > 1
323          );
324 COMMIT;
325 
326 
327 -- DELETE DUPLICATE RECORDS FROM INTERFACE TABLE --
328 
329 
330 DELETE FROM DDR_I_RTL_SHIP_ITEM
331     WHERE
332       (GLBL_ITEM_ID,
333    RTL_BSNS_UNIT_CD,
334    RTL_SKU_ITEM_NBR,
335    GLBL_ITEM_ID_TYP,
336    TRANS_DT)
337     IN (
338      SELECT
339        GLBL_ITEM_ID,
340     RTL_BSNS_UNIT_CD,
341     RTL_SKU_ITEM_NBR,
342     GLBL_ITEM_ID_TYP,
343     TRANS_DT
344     FROM DDR_E_RTL_SHIP_ITEM
345     GROUP BY
346         GLBL_ITEM_ID,
347       RTL_BSNS_UNIT_CD,
348       RTL_SKU_ITEM_NBR,
349       GLBL_ITEM_ID_TYP,
350       TRANS_DT
351          HAVING COUNT(*) > 1
352     );
353 COMMIT;
354 
355 RETURN('Y');
356 
357 END RTL_SHIP_ITEM_DUPS_FNC;
358 
359 FUNCTION RTL_SL_RTN_DUPS_FNC RETURN VARCHAR2
360 AS
361 
362 v_FUNC_MSG VARCHAR2(1) := 'N';
363 
364 BEGIN
365 
366 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
367 
368 
369 INSERT INTO DDR_E_RTL_SL_RTN_ITEM
370     (REC_ID, LOAD_ID, ERR_REASON,
371      GLBL_ITEM_ID, RTL_SKU_ITEM_NBR,
372   GLBL_ITEM_ID_TYP, UOM, SLS_QTY,
373   SLS_AMT, SLS_COST_AMT,RTRN_QTY,
374   RTRN_AMT,RTRN_COST_AMT,SRC_SYS_IDNT,
375   SRC_SYS_DT, PERIOD_TYP_FLAG,
376   LOC_IDNT_CD, LOC_IDNT_FLAG,
377   ORG_LVL_CD,SRC_IDNT_FLAG, ACTION_FLAG,
378   TRANS_DT)
379      SELECT REC_ID, DDR_LOAD_SEQ.NEXTVAL,'Duplicate Record',
380      GLBL_ITEM_ID,RTL_SKU_ITEM_NBR,
381      GLBL_ITEM_ID_TYP, UOM, SLS_QTY,
382      SLS_AMOUNT,SLS_COST_AMOUNT,
383      RTRN_QTY, RTRN_AMOUNT,
384      RTRN_COST_AMOUNT, SRC_SYS_IDNT,
385      SRC_SYS_DT, PERIOD_TYP_FLAG,
386      LOC_IDNT_CD, LOC_IDNT_FLAG,
387      ORG_LVL_CD,'I','N',TRANS_DT
388       FROM DDR_I_RTL_SL_RTN_ITEM
389        WHERE
390            (LOC_IDNT_CD, GLBL_ITEM_ID,
391           RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
392              TRANS_DT, PERIOD_TYP_FLAG,
393           LOC_IDNT_FLAG, ORG_LVL_CD)
394           IN (
395              SELECT
396           LOC_IDNT_CD, GLBL_ITEM_ID,
397           RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
398              TRANS_DT, PERIOD_TYP_FLAG,
399           LOC_IDNT_FLAG, ORG_LVL_CD
400           FROM DDR_I_RTL_SL_RTN_ITEM
401           GROUP BY
402              LOC_IDNT_CD, GLBL_ITEM_ID,
403            RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
404            TRANS_DT, PERIOD_TYP_FLAG,
405            LOC_IDNT_FLAG, ORG_LVL_CD
406              HAVING COUNT(*) > 1
407          );
408 COMMIT;
409 
410 
411 -- DELETE DUPLICATE RECORDS FROM INTERFACE TABLE --
412 
413 
414 DELETE FROM DDR_I_RTL_SL_RTN_ITEM
415     WHERE
416        (LOC_IDNT_CD, GLBL_ITEM_ID,
417     RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
418        TRANS_DT, PERIOD_TYP_FLAG,
419     LOC_IDNT_FLAG, ORG_LVL_CD)
420     IN (
421      SELECT
422        LOC_IDNT_CD, GLBL_ITEM_ID,
423     RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
424        TRANS_DT, PERIOD_TYP_FLAG,
425     LOC_IDNT_FLAG, ORG_LVL_CD
426     FROM DDR_E_RTL_SL_RTN_ITEM
427     GROUP BY
428          LOC_IDNT_CD, GLBL_ITEM_ID,
429        RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
430          TRANS_DT, PERIOD_TYP_FLAG,
431        LOC_IDNT_FLAG, ORG_LVL_CD
432          HAVING COUNT(*) > 1
433     );
434 COMMIT;
435 
436 RETURN('Y');
437 
438 END RTL_SL_RTN_DUPS_FNC;
439 
440 FUNCTION SLS_FRCST_ITEM_DUPS_FNC RETURN VARCHAR2
441 AS
442 
443 BEGIN
444 
445 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
446 
447 
448 INSERT INTO DDR_E_SLS_FRCST_ITEM
449     (REC_ID, LOAD_ID, ERR_REASON,
450     FRCST_SLS_UOM, FRCST_NBR,
451     FRCST_TYP, GLBL_ITEM_ID,
452     RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
453     FRCST_SLS_QTY, FRCST_SLS_AMT,
454     SRC_SYS_IDNT, SRC_SYS_DT,
455     PERIOD_TYP_FLAG, LOC_IDNT_CD,
456     LOC_IDNT_FLAG, ORG_LVL_CD,
457     SRC_IDNT_FLAG, ACTION_FLAG,
458     TRANS_DT
459      )
460     SELECT REC_ID,DDR_LOAD_SEQ.NEXTVAL,'Duplicate Record',
461     FRCST_SLS_UOM, FRCST_NBR,
462     FRCST_TYP, GLBL_ITEM_ID,
463     RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
464     FRCST_SLS_QTY, FRCST_SLS_AMT,
465     SRC_SYS_IDNT, SRC_SYS_DT,
466     PERIOD_TYP_FLAG, LOC_IDNT_CD,
467     LOC_IDNT_FLAG, ORG_LVL_CD,
468     'I','N', TRANS_DT
469       FROM DDR_I_SLS_FRCST_ITEM
470        WHERE
471            (TRANS_DT, FRCST_NBR,
472           FRCST_TYP, LOC_IDNT_CD,
473           GLBL_ITEM_ID, RTL_SKU_ITEM_NBR,
474           GLBL_ITEM_ID_TYP, PERIOD_TYP_FLAG,
475           LOC_IDNT_FLAG, ORG_LVL_CD, SRC_SYS_DT)
476           IN (
477              SELECT
478             TRANS_DT, FRCST_NBR,
479           FRCST_TYP, LOC_IDNT_CD,
480           GLBL_ITEM_ID, RTL_SKU_ITEM_NBR,
481           GLBL_ITEM_ID_TYP, PERIOD_TYP_FLAG,
482           LOC_IDNT_FLAG, ORG_LVL_CD, SRC_SYS_DT
483           FROM DDR_I_SLS_FRCST_ITEM
484           GROUP BY
485               TRANS_DT, FRCST_NBR,
486           FRCST_TYP, LOC_IDNT_CD,
487           GLBL_ITEM_ID, RTL_SKU_ITEM_NBR,
488           GLBL_ITEM_ID_TYP, PERIOD_TYP_FLAG,
489           LOC_IDNT_FLAG, ORG_LVL_CD, SRC_SYS_DT
490              HAVING COUNT(*) > 1
491          );
492 COMMIT;
493 
494 
498 DELETE FROM DDR_I_SLS_FRCST_ITEM
495 -- DELETE DUPLICATE RECORDS FROM INTERFACE TABLE --
496 
497 
499     WHERE
500       (TRANS_DT, FRCST_NBR,
501    FRCST_TYP, LOC_IDNT_CD,
502    GLBL_ITEM_ID, RTL_SKU_ITEM_NBR,
503    GLBL_ITEM_ID_TYP, PERIOD_TYP_FLAG,
504    LOC_IDNT_FLAG, ORG_LVL_CD, SRC_SYS_DT)
505     IN (
506      SELECT
507        TRANS_DT, FRCST_NBR, FRCST_TYP, LOC_IDNT_CD,
508     GLBL_ITEM_ID, RTL_SKU_ITEM_NBR,
509     GLBL_ITEM_ID_TYP, PERIOD_TYP_FLAG,
510     LOC_IDNT_FLAG, ORG_LVL_CD, SRC_SYS_DT
511     FROM DDR_E_SLS_FRCST_ITEM
512     GROUP BY
513         TRANS_DT, FRCST_NBR, FRCST_TYP,
514       LOC_IDNT_CD, GLBL_ITEM_ID,
515       RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP, PERIOD_TYP_FLAG,
516       LOC_IDNT_FLAG, ORG_LVL_CD, SRC_SYS_DT
517          HAVING COUNT(*) > 1
518     );
519 COMMIT;
520 
521 RETURN('Y');
522 
523 END SLS_FRCST_ITEM_DUPS_FNC;
524 
525 FUNCTION decide_dedup_chk RETURN VARCHAR2 IS
526 RT VARCHAR2(5);
527 BEGIN
528 select LKUP_NAME into RT  from DDR_R_LKUP_MST
529 where LKUP_TYP_CD='SYS_PARAM'
530 and LKUP_CD='PERFORM_DUP_CHECK';
531 
532 RETURN RT;
533 END decide_dedup_chk;
534 
535 FUNCTION decide_discover_mode RETURN VARCHAR2 IS
536 RT VARCHAR2(5);
537 BEGIN
538 select LKUP_NAME into RT  from DDR_R_LKUP_MST
539 where LKUP_TYP_CD='SYS_PARAM'
540 and LKUP_CD='DISCOVERY_MODE';
541 RETURN RT;
542 END decide_discover_mode;
543 
544 FUNCTION DECIDE_RUN_MAP_ERR(map_nm IN VARCHAR2, map_stg IN VARCHAR2) RETURN NUMBER IS
545 cnt_rows Number;
546 tab_name_ Varchar2(250);
547 BEGIN
548 cnt_rows := 0;
549 tab_name_ := 'select count(1) from '||map_nm || ', ' || map_stg || ' where '|| map_nm||'.load_id = '|| map_stg || '.load_id and rownum <2';
550 execute immediate tab_name_ into cnt_rows;
551 RETURN cnt_rows;
552 END DECIDE_RUN_MAP_ERR;
553 
554 FUNCTION decide_run_typ RETURN VARCHAR2 IS
555 RT VARCHAR2(5);
556 BEGIN
557 select LKUP_NAME into RT  from DDR_R_LKUP_MST
558 where LKUP_TYP_CD='SYS_PARAM'
559 and LKUP_CD='RUN_TYPE_FACT';
560 
561 RETURN RT;
562 END decide_run_typ;
563 
564 FUNCTION decide_SF_MAP RETURN VARCHAR2 IS
565 RT VARCHAR2(5);
566 BEGIN
567 select LKUP_NAME into RT  from DDR_R_LKUP_MST
568 where LKUP_TYP_CD='SYS_PARAM'
569 and LKUP_CD='STAGE_TO_TARGET_VALIDATION';
570 
571 RETURN RT;
572 END decide_SF_MAP;
573 
574 FUNCTION DECIDE_RUN_CNT_STG(map_nm IN VARCHAR2) RETURN NUMBER IS
575 cnt_rows Number;
576 tab_name_ Varchar2(250);
577 BEGIN
578 cnt_rows := 0;
579 tab_name_ := 'select count(1) from '||map_nm|| ' where rownum <2';
580 execute immediate tab_name_ into cnt_rows;
581 RETURN cnt_rows;
582 END DECIDE_RUN_CNT_STG;
583 
584 FUNCTION DECIDE_RUN_MAP(map_nm IN VARCHAR2) RETURN NUMBER IS
585 cnt_rows Number;
586 tab_name_ Varchar2(250);
587 BEGIN
588 cnt_rows := 0;
589 tab_name_ := 'select count(1) from '||map_nm|| ' where rownum <2';
590 execute immediate tab_name_ into cnt_rows;
591 RETURN cnt_rows;
592 END DECIDE_RUN_MAP;
593 
594 PROCEDURE trunc_tble_pub( p_tbl_name IN VARCHAR2)
595 as
596 Begin
597    EXECUTE IMMEDIATE 'TRUNCATE TABLE '|| 'DDR.' || p_tbl_name;
598 End trunc_tble_pub;
599 
600 END ddr_base_util_pkg;