[Home] [Help]
1: package body PJI_FM_EXTR as
2: /* $Header: PJISF06B.pls 120.12.12020000.4 2013/03/28 13:47:28 krkondur 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);
1266: , to_number(null) AS EOU_UER
1267: , to_number(null) AS QUANTITY
1268: , to_number(null) AS BILL_QUANTITY
1269: , -1 AS CBS_ELEMENT_ID /* Added for CBS Changes */
1270: From PJI_FM_EXTR_DREVN drev
1271: , pa_agreements_all ag
1272: , pa_project_customers cust
1273: , pa_cust_rev_dist_lines_all crdl
1274: Where 1 = 1
1337: ;
1338:
1339: end if; -- EXTRACTION_TYPE = 'FULL'
1340:
1341: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_CRDL_FULL(p_worker_id);');
1342:
1343: commit;
1344:
1345: end EXTRACT_BATCH_CDL_CRDL_FULL;
1358: begin
1359:
1360: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1361:
1362: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL_FULL(p_worker_id);')) then
1363: return;
1364: end if;
1365:
1366: l_min_date := to_date(PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
1504: From pa_agreements_all ag
1505: , pa_project_customers cust
1506: , pa_events ev
1507: , pa_event_types evt
1508: , PJI_FM_EXTR_DREVN drev
1509: , pa_cust_event_rdl_all erdl
1510: Where 1 = 1
1511: And drev.worker_id = p_worker_id
1512: And drev.project_id = erdl.project_id
1541: ;
1542:
1543: end if; -- EXTRACTION_TYPE = 'FULL'
1544:
1545: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL_FULL(p_worker_id);');
1546:
1547: commit;
1548:
1549: end EXTRACT_BATCH_ERDL_FULL;
1562: begin
1563:
1564: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1565:
1566: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_ROWIDS(p_worker_id);')) then
1567: return;
1568: end if;
1569:
1570: if (PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') = 'FULL') then
1667: );
1668:
1669: end if;
1670:
1671: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_ROWIDS(p_worker_id);');
1672:
1673: commit;
1674:
1675: end EXTRACT_BATCH_CDL_ROWIDS;
1686: begin
1687:
1688: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1689:
1690: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CRDL_ROWIDS(p_worker_id);')) then
1691: return;
1692: end if;
1693:
1694: if ( PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') <> 'FULL') then
1696: if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
1697: 'CURRENT_BATCH') = 1) then
1698: -- implicit commit
1699: FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
1700: tabname => 'PJI_FM_EXTR_DREVN',
1701: percent => 10,
1702: degree => PJI_UTILS.
1703: GET_DEGREE_OF_PARALLELISM);
1704: end if;
1736: , drev.line_source_type
1737: , cust.bill_another_project_flag
1738: , cust.customer_id
1739: FROM
1740: PJI_FM_EXTR_DREVN drev
1741: , pa_agreements_all ag
1742: , pa_project_customers cust
1743: , pa_cust_rev_dist_lines_all crdl
1744: WHERE
1753: -- and NVL(cust.bill_another_project_flag,'N') <> 'Y'; -- ER 6519955
1754:
1755: end if; -- EXTRACTION_TYPE <> 'FULL'
1756:
1757: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CRDL_ROWIDS(p_worker_id);');
1758:
1759: commit;
1760:
1761: end EXTRACT_BATCH_CRDL_ROWIDS;
1772: begin
1773:
1774: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1775:
1776: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL_ROWIDS(p_worker_id);')) then
1777: return;
1778: end if;
1779:
1780: if ( PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') <> 'FULL') then
1830: , drev.pa_period_name pa_period_name
1831: , drev.gl_date gl_date
1832: , drev.gl_period_name gl_period_name
1833: FROM
1834: PJI_FM_EXTR_DREVN drev
1835: , pa_agreements_all ag
1836: , pa_project_customers cust
1837: , pa_cust_event_rdl_all erdl /* Changed the order for bug 8668173 */
1838: , pa_events ev
1855: ;
1856:
1857: end if; -- EXTRACTION_TYPE <> 'FULL'
1858:
1859: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL_ROWIDS(p_worker_id);');
1860:
1861: commit;
1862:
1863: end EXTRACT_BATCH_ERDL_ROWIDS;
1876: begin
1877:
1878: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1879:
1880: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_AND_CRDL(p_worker_id);')) then
1881: return;
1882: end if;
1883:
1884: l_min_date := to_date(PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
2342: ;
2343:
2344: end if; -- EXTRACTION_TYPE <> 'FULL'
2345:
2346: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_AND_CRDL(p_worker_id);');
2347:
2348: -- truncate intermediate tables no longer required
2349: l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
2350: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_CRDL' , 'NORMAL',null);
2365:
2366: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2367:
2368: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
2369: 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS_PRE(p_worker_id);')) then
2370: return;
2371: end if;
2372:
2373: insert /*+ append */ into PJI_HELPER_BATCH_MAP
2386: where
2387: PJI_SUMMARIZED_FLAG is not null;
2388:
2389: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
2390: 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS_PRE(p_worker_id);');
2391:
2392: commit;
2393:
2394: end MARK_EXTRACTED_CDL_ROWS_PRE;
2408: begin
2409:
2410: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2411:
2412: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS(p_worker_id);')) then
2413: return;
2414: end if;
2415:
2416: l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2482: update PJI_SYSTEM_PRC_STATUS
2483: set STEP_STATUS = 'C'
2484: where PROCESS_NAME like PJI_FM_SUM_MAIN.g_process || x and
2485: STEP_NAME =
2486: 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS(p_worker_id);' and
2487: START_DATE is null;
2488:
2489: commit;
2490:
2514:
2515: if (l_helper_batch_id <> -2) then
2516:
2517: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
2518: 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS(p_worker_id);');
2519:
2520: end if;
2521:
2522: commit;
2535:
2536: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2537:
2538: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
2539: 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS_POST(p_worker_id);')) then
2540: return;
2541: end if;
2542:
2543: PJI_PROCESS_UTIL.TRUNC_INT_TABLE('PJI',
2545: 'NORMAL',
2546: null);
2547:
2548: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
2549: 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS_POST(p_worker_id);');
2550:
2551: if (PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') = 'FULL') then
2552: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(PJI_UTILS.GET_PJI_SCHEMA_NAME,
2553: 'PJI_FM_REXT_CDL', 'NORMAL',null);
2570: begin
2571:
2572: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2573:
2574: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL(p_worker_id);')) then
2575: return;
2576: end if;
2577:
2578: l_min_date := to_date(PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
2844: ;
2845:
2846: end if; -- EXTRACTION_TYPE <> 'FULL'
2847:
2848: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL(p_worker_id);');
2849:
2850: -- truncate intermediate tables no longer required
2851: l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
2852: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_ERDL' , 'NORMAL',null);
2869: begin
2870:
2871: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2872:
2873: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DINV(p_worker_id);')) then
2874: return;
2875: end if;
2876:
2877: l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
2875: end if;
2876:
2877: l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
2878:
2879: INSERT /*+ APPEND */ INTO PJI_FM_EXTR_DINVC
2880: ( WORKER_ID
2881: , ROW_ID
2882: , PROJECT_ORG_ID
2883: , PROJECT_ORGANIZATION_ID
3153: and dinv.system_reference is not null
3154: and dinv.system_reference <> 0
3155: and dinv.agreement_id = agr.agreement_id;
3156:
3157: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DINV(p_worker_id);');
3158:
3159: commit;
3160:
3161: end EXTRACT_BATCH_DINV;
3172:
3173: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3174: l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
3175:
3176: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.MARK_EXTRACTED_DINV_ROWS(p_worker_id);')) then
3177: return;
3178: end if;
3179:
3180: UPDATE pa_draft_invoices_all dinv
3180: UPDATE pa_draft_invoices_all dinv
3181: SET dinv.pji_summarized_flag = 'O'
3182: -- later the flag is updated to null for those invoices that are closed
3183: WHERE dinv.rowid in (select row_id
3184: from PJI_FM_EXTR_DINVC
3185: where worker_id = p_worker_id
3186: and transfer_status_code = 'A'
3187: )
3188: AND ( (l_extraction_type = 'INCREMENTAL'
3191: l_extraction_type <> 'INCREMENTAL'
3192: )
3193: ;
3194:
3195: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.MARK_EXTRACTED_DINV_ROWS(p_worker_id);');
3196:
3197: commit;
3198:
3199: end MARK_EXTRACTED_DINV_ROWS;
3212: begin
3213:
3214: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3215:
3216: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DINVITEM(p_worker_id);')) then
3217: return;
3218: end if;
3219:
3220: if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
3220: if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
3221: 'CURRENT_BATCH') = 1) then
3222: -- implicit commit
3223: FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
3224: tabname => 'PJI_FM_EXTR_DINVC',
3225: percent => 10,
3226: degree => PJI_UTILS.
3227: GET_DEGREE_OF_PARALLELISM);
3228: -- implicit commit
3226: degree => PJI_UTILS.
3227: GET_DEGREE_OF_PARALLELISM);
3228: -- implicit commit
3229: FND_STATS.GATHER_COLUMN_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
3230: tabname => 'PJI_FM_EXTR_DINVC',
3231: colname => 'PROJECT_ID',
3232: percent => 10,
3233: degree => PJI_UTILS.
3234: GET_DEGREE_OF_PARALLELISM);
3235: end if;
3236:
3237: l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
3238:
3239: INSERT /*+ APPEND */ INTO PJI_FM_EXTR_DINVCITM
3240: ( WORKER_ID
3241: , PROJECT_ORG_ID
3242: , PROJECT_ORGANIZATION_ID
3243: , PROJECT_ID
3288: , to_number(null) ar_credit_memo_count
3289: , to_number(null) ar_unappr_invoice_count
3290: , to_number(null) ar_appr_invoice_count
3291: FROM
3292: PJI_FM_EXTR_DINVC part
3293: , pa_draft_invoice_items item
3294: WHERE
3295: l_extraction_type = 'FULL'
3296: and part.worker_id = p_worker_id
3341: , to_number(null) ar_credit_memo_count
3342: , to_number(null) ar_unappr_invoice_count
3343: , to_number(null) ar_appr_invoice_count
3344: FROM
3345: PJI_FM_EXTR_DINVC part
3346: , pa_draft_invoice_items item
3347: WHERE
3348: l_extraction_type = 'INCREMENTAL'
3349: and part.worker_id = p_worker_id
3394: , to_number(null) ar_credit_memo_count
3395: , to_number(null) ar_unappr_invoice_count
3396: , to_number(null) ar_appr_invoice_count
3397: FROM
3398: PJI_FM_EXTR_DINVC part
3399: , pa_draft_invoice_items item
3400: WHERE
3401: l_extraction_type = 'PARTIAL'
3402: and part.worker_id = p_worker_id
3462: 'A','A','S') || '_' ||
3463: part.approved_flag,
3464: 'S_Y',1,0) ar_appr_invoice_count
3465: FROM
3466: PJI_FM_EXTR_DINVC part
3467: WHERE
3468: part.worker_id = p_worker_id
3469: and part.gl_date is not null
3470: and part.pa_date is not null;
3468: part.worker_id = p_worker_id
3469: and part.gl_date is not null
3470: and part.pa_date is not null;
3471:
3472: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DINVITEM(p_worker_id);');
3473:
3474: commit;
3475:
3476: end EXTRACT_BATCH_DINVITEM;
3487: begin
3488:
3489: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3490:
3491: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ARINV(p_worker_id);')) then
3492: return;
3493: end if;
3494:
3495: l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
3493: end if;
3494:
3495: l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
3496:
3497: INSERT /*+ APPEND */ INTO PJI_FM_EXTR_ARINV
3498: ( WORKER_ID
3499: , ROW_ID
3500: , PROJECT_ID
3501: , PROJECT_ORG_ID
3564: ar.due_date,
3565: trx.bill_to_customer_id,
3566: part.pji_summarized_flag);
3567:
3568: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ARINV(p_worker_id);');
3569:
3570: commit;
3571:
3572: end EXTRACT_BATCH_ARINV;
3583:
3584: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3585:
3586: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
3587: 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES_PRE(p_worker_id);')) then
3588: return;
3589: end if;
3590:
3591: insert /*+ append */ into PJI_HELPER_BATCH_MAP
3599: BATCH_ID,
3600: null,
3601: null
3602: from
3603: PJI_FM_EXTR_ARINV
3604: where
3605: PJI_SUMMARIZED_FLAG = 'O';
3606:
3607: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
3604: where
3605: PJI_SUMMARIZED_FLAG = 'O';
3606:
3607: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
3608: 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES_PRE(p_worker_id);');
3609:
3610: commit;
3611:
3612: end MARK_FULLY_PAID_INVOICES_PRE;
3626: begin
3627:
3628: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3629:
3630: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES(p_worker_id);')) then
3631: return;
3632: end if;
3633:
3634: l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3674: UPDATE pa_draft_invoices_all dinv
3675: SET dinv.pji_summarized_flag = NULL
3676: WHERE dinv.rowid in (SELECT /*+ cardinality(ar, 1) */
3677: ar.row_id
3678: FROM PJI_FM_EXTR_ARINV ar
3679: WHERE 1 = 2 -- We will always extract
3680: -- the AR snapshots for now.
3681: AND ar.pji_summarized_flag = 'O'
3682: AND ar.batch_id = l_helper_batch_id);
3702: update PJI_SYSTEM_PRC_STATUS
3703: set STEP_STATUS = 'C'
3704: where PROCESS_NAME like PJI_FM_SUM_MAIN.g_process || x and
3705: STEP_NAME =
3706: 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES(p_worker_id);' and
3707: START_DATE is null;
3708:
3709: commit;
3710:
3734:
3735: if (l_helper_batch_id <> -2) then
3736:
3737: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
3738: 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES(p_worker_id);');
3739:
3740: end if;
3741:
3742: commit;
3755:
3756: l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3757:
3758: if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
3759: 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES_POST(p_worker_id);')) then
3760: return;
3761: end if;
3762:
3763: PJI_PROCESS_UTIL.TRUNC_INT_TABLE('PJI',
3765: 'NORMAL',
3766: null);
3767:
3768: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
3769: 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES_POST(p_worker_id);');
3770:
3771: commit;
3772:
3773: end MARK_FULLY_PAID_INVOICES_POST;
3783: begin
3784:
3785: l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
3786:
3787: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_FUNDG', 'NORMAL',null);
3788:
3789: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DREVN', 'NORMAL',null);
3790:
3791: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_CDL', 'NORMAL',null);
3785: l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
3786:
3787: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_FUNDG', 'NORMAL',null);
3788:
3789: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DREVN', 'NORMAL',null);
3790:
3791: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_CDL', 'NORMAL',null);
3792:
3793: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_CRDL', 'NORMAL',null);
3793: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_CRDL', 'NORMAL',null);
3794:
3795: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_ERDL', 'NORMAL',null);
3796:
3797: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DINVC', 'NORMAL',null);
3798:
3799: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DINVCITM', 'NORMAL',null);
3800:
3801: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_ARINV', 'NORMAL',null);
3795: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_ERDL', 'NORMAL',null);
3796:
3797: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DINVC', 'NORMAL',null);
3798:
3799: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DINVCITM', 'NORMAL',null);
3800:
3801: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_ARINV', 'NORMAL',null);
3802:
3803: end CLEANUP;
3797: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DINVC', 'NORMAL',null);
3798:
3799: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DINVCITM', 'NORMAL',null);
3800:
3801: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_ARINV', 'NORMAL',null);
3802:
3803: end CLEANUP;
3804:
3805: end PJI_FM_EXTR;
3801: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_ARINV', 'NORMAL',null);
3802:
3803: end CLEANUP;
3804:
3805: end PJI_FM_EXTR;