[Home] [Help]
1: package body PJI_FM_EXTR as
2: /* $Header: PJISF06B.pls 120.9.12010000.2 2008/09/17 11:00:50 rballamu ship $ */
3:
4: -- -----------------------------------------------------
5: -- procedure EXTRACT_BATCH_FND
14: begin
15:
16: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
17:
18: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_FND(p_worker_id);')) then
19: return;
20: end if;
21:
22: l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
20: end if;
21:
22: l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
23:
24: INSERT /*+ APPEND */ INTO PJI_FM_EXTR_FUNDG
25: (
26: worker_id
27: ,project_org_id
28: ,project_organization_id
165: AND agr.customer_id = cust.customer_id
166: -- AND NVL(cust.bill_another_project_flag,'N') <> 'Y' -- ER 6519955
167: AND pf.date_allocated is not null;
168:
169: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_FND(p_worker_id);');
170:
171: commit;
172:
173: end EXTRACT_BATCH_FND;
184:
185: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
186:
187: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
188: 'PJI_FM_EXTR.MARK_EXTRACTED_FND_ROWS_PRE(p_worker_id);')) then
189: return;
190: end if;
191:
192: insert /*+ append */ into PJI_HELPER_BATCH_MAP
200: BATCH_ID,
201: null,
202: null
203: from
204: PJI_FM_EXTR_FUNDG
205: where
206: PJI_SUMMARIZED_FLAG is not null;
207:
208: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
205: where
206: PJI_SUMMARIZED_FLAG is not null;
207:
208: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
209: 'PJI_FM_EXTR.MARK_EXTRACTED_FND_ROWS_PRE(p_worker_id);');
210:
211: commit;
212:
213: end MARK_EXTRACTED_FND_ROWS_PRE;
227: begin
228:
229: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
230:
231: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.MARK_EXTRACTED_FND_ROWS(p_worker_id);')) then
232: return;
233: end if;
234:
235: l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
275: update pa_project_fundings
276: set pji_summarized_flag = NULL
277: where rowid in (select /*+ cardinality(fnd, 1) */
278: fnd.row_id
279: from PJI_FM_EXTR_FUNDG fnd
280: where fnd.pji_summarized_flag is not null and
281: fnd.batch_id = l_helper_batch_id);
282:
283: update PJI_HELPER_BATCH_MAP
301: update PJI_SYSTEM_PRC_STATUS
302: set STEP_STATUS = 'C'
303: where PROCESS_NAME like PJI_FM_SUM_MAIN.g_process || x and
304: STEP_NAME =
305: 'PJI_FM_EXTR.MARK_EXTRACTED_FND_ROWS(p_worker_id);' and
306: START_DATE is null;
307:
308: commit;
309:
333:
334: if (l_helper_batch_id <> -2) then
335:
336: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
337: 'PJI_FM_EXTR.MARK_EXTRACTED_FND_ROWS(p_worker_id);');
338:
339: end if;
340:
341: commit;
354:
355: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
356:
357: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
358: 'PJI_FM_EXTR.MARK_EXTRACTED_FND_ROWS_POST(p_worker_id);')) then
359: return;
360: end if;
361:
362: PJI_PROCESS_UTIL.TRUNC_INT_TABLE('PJI',
364: 'NORMAL',
365: null);
366:
367: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
368: 'PJI_FM_EXTR.MARK_EXTRACTED_FND_ROWS_POST(p_worker_id);');
369:
370: commit;
371:
372: end MARK_EXTRACTED_FND_ROWS_POST;
386: begin
387:
388: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
389:
390: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DREV(p_worker_id);')) then
391: return;
392: end if;
393:
394: l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
396: l_transition_flag :=
397: PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_FM_SUM_MAIN.g_process,
398: 'TRANSITION');
399:
400: INSERT /*+ APPEND */ INTO PJI_FM_EXTR_DREVN
401: (
402: ROW_ID
403: , WORKER_ID
404: , LINE_SOURCE_TYPE
634: and to_date(log.attribute6, PJI_FM_SUM_MAIN.g_date_mask)
635: between glprd.START_DATE and glprd.END_DATE
636: ;
637:
638: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DREV(p_worker_id);');
639:
640: commit;
641:
642: end EXTRACT_BATCH_DREV;
653:
654: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
655:
656: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
657: 'PJI_FM_EXTR.MARK_EXTRACTED_DREV_PRE(p_worker_id);')) then
658: return;
659: end if;
660:
661: insert /*+ append */ into PJI_HELPER_BATCH_MAP
669: BATCH_ID,
670: null,
671: null
672: from
673: PJI_FM_EXTR_DREVN
674: where
675: (LINE_SOURCE_TYPE = 'R' and
676: PJI_SUMMARIZED_FLAG is not null) or
677: (LINE_SOURCE_TYPE = 'L');
676: PJI_SUMMARIZED_FLAG is not null) or
677: (LINE_SOURCE_TYPE = 'L');
678:
679: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
680: 'PJI_FM_EXTR.MARK_EXTRACTED_DREV_PRE(p_worker_id);');
681:
682: commit;
683:
684: end MARK_EXTRACTED_DREV_PRE;
698: begin
699:
700: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
701:
702: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.MARK_EXTRACTED_DREV(p_worker_id);')) then
703: return;
704: end if;
705:
706: l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
746: UPDATE pa_draft_revenues_all drv
747: SET drv.pji_summarized_flag = null
748: WHERE drv.rowid in (select /*+ cardinality(drvn, 1) */
749: drvn.row_id
750: from PJI_FM_EXTR_DREVN drvn
751: where drvn.pji_summarized_flag is not null
752: and drvn.LINE_SOURCE_TYPE = 'R'
753: and drvn.batch_id = l_helper_batch_id);
754:
755: -- Clean up log table
756:
757: DELETE pa_pji_proj_events_log
758: WHERE rowid in (select row_id
759: from PJI_FM_EXTR_DREVN
760: where line_source_type = 'L'
761: and batch_id = l_helper_batch_id);
762:
763: update PJI_HELPER_BATCH_MAP
781: update PJI_SYSTEM_PRC_STATUS
782: set STEP_STATUS = 'C'
783: where PROCESS_NAME like PJI_FM_SUM_MAIN.g_process || x and
784: STEP_NAME =
785: 'PJI_FM_EXTR.MARK_EXTRACTED_DREV(p_worker_id);' and
786: START_DATE is null;
787:
788: commit;
789:
813:
814: if (l_helper_batch_id <> -2) then
815:
816: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
817: 'PJI_FM_EXTR.MARK_EXTRACTED_DREV(p_worker_id);');
818:
819: end if;
820:
821: commit;
834:
835: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
836:
837: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
838: 'PJI_FM_EXTR.MARK_EXTRACTED_DREV_POST(p_worker_id);')) then
839: return;
840: end if;
841:
842: PJI_PROCESS_UTIL.TRUNC_INT_TABLE('PJI',
844: 'NORMAL',
845: null);
846:
847: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
848: 'PJI_FM_EXTR.MARK_EXTRACTED_DREV_POST(p_worker_id);');
849:
850: commit;
851:
852: end MARK_EXTRACTED_DREV_POST;
865: begin
866:
867: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
868:
869: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_CRDL_FULL(p_worker_id);')) then
870: return;
871: end if;
872:
873: l_min_date := to_date(PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
894: if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
895: 'CURRENT_BATCH') = 1) then
896: -- implicit commit
897: FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
898: tabname => 'PJI_FM_EXTR_DREVN',
899: percent => 10,
900: degree => PJI_UTILS.
901: GET_DEGREE_OF_PARALLELISM);
902: -- implicit commit
900: degree => PJI_UTILS.
901: GET_DEGREE_OF_PARALLELISM);
902: -- implicit commit
903: FND_STATS.GATHER_COLUMN_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
904: tabname => 'PJI_FM_EXTR_DREVN',
905: colname => 'PROJECT_ID',
906: percent => 10,
907: degree => PJI_UTILS.
908: GET_DEGREE_OF_PARALLELISM);
1260: , to_number(null) AS EOU_UBR
1261: , to_number(null) AS EOU_UER
1262: , to_number(null) AS QUANTITY
1263: , to_number(null) AS BILL_QUANTITY
1264: From PJI_FM_EXTR_DREVN drev
1265: , pa_agreements_all ag
1266: , pa_project_customers cust
1267: , pa_cust_rev_dist_lines_all crdl
1268: Where 1 = 1
1330: ;
1331:
1332: end if; -- EXTRACTION_TYPE = 'FULL'
1333:
1334: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_CRDL_FULL(p_worker_id);');
1335:
1336: commit;
1337:
1338: end EXTRACT_BATCH_CDL_CRDL_FULL;
1351: begin
1352:
1353: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1354:
1355: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL_FULL(p_worker_id);')) then
1356: return;
1357: end if;
1358:
1359: l_min_date := to_date(PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
1497: From pa_agreements_all ag
1498: , pa_project_customers cust
1499: , pa_events ev
1500: , pa_event_types evt
1501: , PJI_FM_EXTR_DREVN drev
1502: , pa_cust_event_rdl_all erdl
1503: Where 1 = 1
1504: And drev.worker_id = p_worker_id
1505: And drev.project_id = erdl.project_id
1534: ;
1535:
1536: end if; -- EXTRACTION_TYPE = 'FULL'
1537:
1538: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL_FULL(p_worker_id);');
1539:
1540: commit;
1541:
1542: end EXTRACT_BATCH_ERDL_FULL;
1555: begin
1556:
1557: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1558:
1559: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_ROWIDS(p_worker_id);')) then
1560: return;
1561: end if;
1562:
1563: if (PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') = 'FULL') then
1660: );
1661:
1662: end if;
1663:
1664: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_ROWIDS(p_worker_id);');
1665:
1666: commit;
1667:
1668: end EXTRACT_BATCH_CDL_ROWIDS;
1679: begin
1680:
1681: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1682:
1683: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CRDL_ROWIDS(p_worker_id);')) then
1684: return;
1685: end if;
1686:
1687: if ( PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') <> 'FULL') then
1689: if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
1690: 'CURRENT_BATCH') = 1) then
1691: -- implicit commit
1692: FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
1693: tabname => 'PJI_FM_EXTR_DREVN',
1694: percent => 10,
1695: degree => PJI_UTILS.
1696: GET_DEGREE_OF_PARALLELISM);
1697: end if;
1729: , drev.line_source_type
1730: , cust.bill_another_project_flag
1731: , cust.customer_id
1732: FROM
1733: PJI_FM_EXTR_DREVN drev
1734: , pa_agreements_all ag
1735: , pa_project_customers cust
1736: , pa_cust_rev_dist_lines_all crdl
1737: WHERE
1746: -- and NVL(cust.bill_another_project_flag,'N') <> 'Y'; -- ER 6519955
1747:
1748: end if; -- EXTRACTION_TYPE <> 'FULL'
1749:
1750: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CRDL_ROWIDS(p_worker_id);');
1751:
1752: commit;
1753:
1754: end EXTRACT_BATCH_CRDL_ROWIDS;
1765: begin
1766:
1767: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1768:
1769: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL_ROWIDS(p_worker_id);')) then
1770: return;
1771: end if;
1772:
1773: if ( PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') <> 'FULL') then
1823: , drev.pa_period_name pa_period_name
1824: , drev.gl_date gl_date
1825: , drev.gl_period_name gl_period_name
1826: FROM
1827: PJI_FM_EXTR_DREVN drev
1828: , pa_agreements_all ag
1829: , pa_project_customers cust
1830: , pa_events ev
1831: , pa_event_types evt
1848: ;
1849:
1850: end if; -- EXTRACTION_TYPE <> 'FULL'
1851:
1852: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL_ROWIDS(p_worker_id);');
1853:
1854: commit;
1855:
1856: end EXTRACT_BATCH_ERDL_ROWIDS;
1869: begin
1870:
1871: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1872:
1873: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_AND_CRDL(p_worker_id);')) then
1874: return;
1875: end if;
1876:
1877: l_min_date := to_date(PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
2329: ;
2330:
2331: end if; -- EXTRACTION_TYPE <> 'FULL'
2332:
2333: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_AND_CRDL(p_worker_id);');
2334:
2335: -- truncate intermediate tables no longer required
2336: l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
2337: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_CRDL' , 'NORMAL',null);
2352:
2353: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2354:
2355: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
2356: 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS_PRE(p_worker_id);')) then
2357: return;
2358: end if;
2359:
2360: insert /*+ append */ into PJI_HELPER_BATCH_MAP
2373: where
2374: PJI_SUMMARIZED_FLAG is not null;
2375:
2376: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
2377: 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS_PRE(p_worker_id);');
2378:
2379: commit;
2380:
2381: end MARK_EXTRACTED_CDL_ROWS_PRE;
2395: begin
2396:
2397: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2398:
2399: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS(p_worker_id);')) then
2400: return;
2401: end if;
2402:
2403: l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2469: update PJI_SYSTEM_PRC_STATUS
2470: set STEP_STATUS = 'C'
2471: where PROCESS_NAME like PJI_FM_SUM_MAIN.g_process || x and
2472: STEP_NAME =
2473: 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS(p_worker_id);' and
2474: START_DATE is null;
2475:
2476: commit;
2477:
2501:
2502: if (l_helper_batch_id <> -2) then
2503:
2504: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
2505: 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS(p_worker_id);');
2506:
2507: end if;
2508:
2509: commit;
2522:
2523: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2524:
2525: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
2526: 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS_POST(p_worker_id);')) then
2527: return;
2528: end if;
2529:
2530: PJI_PROCESS_UTIL.TRUNC_INT_TABLE('PJI',
2532: 'NORMAL',
2533: null);
2534:
2535: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
2536: 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS_POST(p_worker_id);');
2537:
2538: if (PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') = 'FULL') then
2539: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(PJI_UTILS.GET_PJI_SCHEMA_NAME,
2540: 'PJI_FM_REXT_CDL', 'NORMAL',null);
2557: begin
2558:
2559: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2560:
2561: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL(p_worker_id);')) then
2562: return;
2563: end if;
2564:
2565: l_min_date := to_date(PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
2831: ;
2832:
2833: end if; -- EXTRACTION_TYPE <> 'FULL'
2834:
2835: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL(p_worker_id);');
2836:
2837: -- truncate intermediate tables no longer required
2838: l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
2839: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_ERDL' , 'NORMAL',null);
2856: begin
2857:
2858: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2859:
2860: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DINV(p_worker_id);')) then
2861: return;
2862: end if;
2863:
2864: l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
2862: end if;
2863:
2864: l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
2865:
2866: INSERT /*+ APPEND */ INTO PJI_FM_EXTR_DINVC
2867: ( WORKER_ID
2868: , ROW_ID
2869: , PROJECT_ORG_ID
2870: , PROJECT_ORGANIZATION_ID
3140: and dinv.system_reference is not null
3141: and dinv.system_reference <> 0
3142: and dinv.agreement_id = agr.agreement_id;
3143:
3144: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DINV(p_worker_id);');
3145:
3146: commit;
3147:
3148: end EXTRACT_BATCH_DINV;
3159:
3160: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3161: l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
3162:
3163: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.MARK_EXTRACTED_DINV_ROWS(p_worker_id);')) then
3164: return;
3165: end if;
3166:
3167: UPDATE pa_draft_invoices_all dinv
3167: UPDATE pa_draft_invoices_all dinv
3168: SET dinv.pji_summarized_flag = 'O'
3169: -- later the flag is updated to null for those invoices that are closed
3170: WHERE dinv.rowid in (select row_id
3171: from PJI_FM_EXTR_DINVC
3172: where worker_id = p_worker_id
3173: and transfer_status_code = 'A'
3174: )
3175: AND ( (l_extraction_type = 'INCREMENTAL'
3178: l_extraction_type <> 'INCREMENTAL'
3179: )
3180: ;
3181:
3182: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.MARK_EXTRACTED_DINV_ROWS(p_worker_id);');
3183:
3184: commit;
3185:
3186: end MARK_EXTRACTED_DINV_ROWS;
3199: begin
3200:
3201: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3202:
3203: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DINVITEM(p_worker_id);')) then
3204: return;
3205: end if;
3206:
3207: if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
3207: if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
3208: 'CURRENT_BATCH') = 1) then
3209: -- implicit commit
3210: FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
3211: tabname => 'PJI_FM_EXTR_DINVC',
3212: percent => 10,
3213: degree => PJI_UTILS.
3214: GET_DEGREE_OF_PARALLELISM);
3215: -- implicit commit
3213: degree => PJI_UTILS.
3214: GET_DEGREE_OF_PARALLELISM);
3215: -- implicit commit
3216: FND_STATS.GATHER_COLUMN_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
3217: tabname => 'PJI_FM_EXTR_DINVC',
3218: colname => 'PROJECT_ID',
3219: percent => 10,
3220: degree => PJI_UTILS.
3221: GET_DEGREE_OF_PARALLELISM);
3222: end if;
3223:
3224: l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
3225:
3226: INSERT /*+ APPEND */ INTO PJI_FM_EXTR_DINVCITM
3227: ( WORKER_ID
3228: , PROJECT_ORG_ID
3229: , PROJECT_ORGANIZATION_ID
3230: , PROJECT_ID
3275: , to_number(null) ar_credit_memo_count
3276: , to_number(null) ar_unappr_invoice_count
3277: , to_number(null) ar_appr_invoice_count
3278: FROM
3279: PJI_FM_EXTR_DINVC part
3280: , pa_draft_invoice_items item
3281: WHERE
3282: l_extraction_type = 'FULL'
3283: and part.worker_id = p_worker_id
3328: , to_number(null) ar_credit_memo_count
3329: , to_number(null) ar_unappr_invoice_count
3330: , to_number(null) ar_appr_invoice_count
3331: FROM
3332: PJI_FM_EXTR_DINVC part
3333: , pa_draft_invoice_items item
3334: WHERE
3335: l_extraction_type = 'INCREMENTAL'
3336: and part.worker_id = p_worker_id
3381: , to_number(null) ar_credit_memo_count
3382: , to_number(null) ar_unappr_invoice_count
3383: , to_number(null) ar_appr_invoice_count
3384: FROM
3385: PJI_FM_EXTR_DINVC part
3386: , pa_draft_invoice_items item
3387: WHERE
3388: l_extraction_type = 'PARTIAL'
3389: and part.worker_id = p_worker_id
3449: 'A','A','S') || '_' ||
3450: part.approved_flag,
3451: 'S_Y',1,0) ar_appr_invoice_count
3452: FROM
3453: PJI_FM_EXTR_DINVC part
3454: WHERE
3455: part.worker_id = p_worker_id
3456: and part.gl_date is not null
3457: and part.pa_date is not null;
3455: part.worker_id = p_worker_id
3456: and part.gl_date is not null
3457: and part.pa_date is not null;
3458:
3459: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DINVITEM(p_worker_id);');
3460:
3461: commit;
3462:
3463: end EXTRACT_BATCH_DINVITEM;
3474: begin
3475:
3476: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3477:
3478: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ARINV(p_worker_id);')) then
3479: return;
3480: end if;
3481:
3482: l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
3480: end if;
3481:
3482: l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
3483:
3484: INSERT /*+ APPEND */ INTO PJI_FM_EXTR_ARINV
3485: ( WORKER_ID
3486: , ROW_ID
3487: , PROJECT_ID
3488: , PROJECT_ORG_ID
3551: ar.due_date,
3552: trx.bill_to_customer_id,
3553: part.pji_summarized_flag);
3554:
3555: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ARINV(p_worker_id);');
3556:
3557: commit;
3558:
3559: end EXTRACT_BATCH_ARINV;
3570:
3571: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3572:
3573: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
3574: 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES_PRE(p_worker_id);')) then
3575: return;
3576: end if;
3577:
3578: insert /*+ append */ into PJI_HELPER_BATCH_MAP
3586: BATCH_ID,
3587: null,
3588: null
3589: from
3590: PJI_FM_EXTR_ARINV
3591: where
3592: PJI_SUMMARIZED_FLAG = 'O';
3593:
3594: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
3591: where
3592: PJI_SUMMARIZED_FLAG = 'O';
3593:
3594: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
3595: 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES_PRE(p_worker_id);');
3596:
3597: commit;
3598:
3599: end MARK_FULLY_PAID_INVOICES_PRE;
3613: begin
3614:
3615: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3616:
3617: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES(p_worker_id);')) then
3618: return;
3619: end if;
3620:
3621: l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3661: UPDATE pa_draft_invoices_all dinv
3662: SET dinv.pji_summarized_flag = NULL
3663: WHERE dinv.rowid in (SELECT /*+ cardinality(ar, 1) */
3664: ar.row_id
3665: FROM PJI_FM_EXTR_ARINV ar
3666: WHERE 1 = 2 -- We will always extract
3667: -- the AR snapshots for now.
3668: AND ar.pji_summarized_flag = 'O'
3669: AND ar.batch_id = l_helper_batch_id);
3689: update PJI_SYSTEM_PRC_STATUS
3690: set STEP_STATUS = 'C'
3691: where PROCESS_NAME like PJI_FM_SUM_MAIN.g_process || x and
3692: STEP_NAME =
3693: 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES(p_worker_id);' and
3694: START_DATE is null;
3695:
3696: commit;
3697:
3721:
3722: if (l_helper_batch_id <> -2) then
3723:
3724: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
3725: 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES(p_worker_id);');
3726:
3727: end if;
3728:
3729: commit;
3742:
3743: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3744:
3745: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
3746: 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES_POST(p_worker_id);')) then
3747: return;
3748: end if;
3749:
3750: PJI_PROCESS_UTIL.TRUNC_INT_TABLE('PJI',
3752: 'NORMAL',
3753: null);
3754:
3755: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
3756: 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES_POST(p_worker_id);');
3757:
3758: commit;
3759:
3760: end MARK_FULLY_PAID_INVOICES_POST;
3770: begin
3771:
3772: l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
3773:
3774: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_FUNDG', 'NORMAL',null);
3775:
3776: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DREVN', 'NORMAL',null);
3777:
3778: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_CDL', 'NORMAL',null);
3772: l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
3773:
3774: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_FUNDG', 'NORMAL',null);
3775:
3776: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DREVN', 'NORMAL',null);
3777:
3778: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_CDL', 'NORMAL',null);
3779:
3780: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_CRDL', 'NORMAL',null);
3780: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_CRDL', 'NORMAL',null);
3781:
3782: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_ERDL', 'NORMAL',null);
3783:
3784: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DINVC', 'NORMAL',null);
3785:
3786: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DINVCITM', 'NORMAL',null);
3787:
3788: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_ARINV', 'NORMAL',null);
3782: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_ERDL', 'NORMAL',null);
3783:
3784: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DINVC', 'NORMAL',null);
3785:
3786: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DINVCITM', 'NORMAL',null);
3787:
3788: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_ARINV', 'NORMAL',null);
3789:
3790: end CLEANUP;
3784: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DINVC', 'NORMAL',null);
3785:
3786: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DINVCITM', 'NORMAL',null);
3787:
3788: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_ARINV', 'NORMAL',null);
3789:
3790: end CLEANUP;
3791:
3792: end PJI_FM_EXTR;
3788: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_ARINV', 'NORMAL',null);
3789:
3790: end CLEANUP;
3791:
3792: end PJI_FM_EXTR;