[Home] [Help]
37: l_msg_data VARCHAR2(4000);
38:
39: l_txn_count NUMBER;
40:
41: --Cursor to check if there are transactions with no entries in the dpp_execution_processes table
42: CURSOR validTransactionsCur
43: IS
44: SELECT count(1)
45: FROM dpp_transaction_headers_all dpp
44: SELECT count(1)
45: FROM dpp_transaction_headers_all dpp
46: WHERE NOT EXISTS
47: (SELECT dep.transaction_header_id
48: FROM dpp_execution_processes dep
49: WHERE dep.transaction_header_id = dpp.transaction_header_id);
50:
51:
52: BEGIN
73: OPEN validTransactionsCur;
74: FETCH validTransactionsCur INTO l_txn_count;
75: CLOSE validTransactionsCur;
76:
77: fnd_file.put_line(fnd_file.log, ' Transactions with no entries in the dpp_execution_processes table - ' || l_txn_count );
78:
79: IF l_txn_count = 0 THEN
80: IF G_DEBUG THEN
81: fnd_file.put_line(fnd_file.log, ' Migration has already been performed. ' );
434: -- PROCEDURE
435: -- insertExecutionProcesses
436: --
437: -- PURPOSE
438: -- This procedure inserts records into the DPP_EXECUTION_PROCESSES
439: -- for those transactions for which the entries does not exist. Entires
440: -- are based on either supplier trade profile or system parameters
441: -- in Trade Management.
442: --
471: l_org_id NUMBER;
472:
473: l_count NUMBER := 0;
474:
475: TYPE ProcessCodeTab IS TABLE OF DPP_EXECUTION_PROCESSES.PROCESS_CODE%TYPE INDEX BY PLS_INTEGER;
476: process_codes ProcessCodeTab;
477:
478: --Cursor to fetch those transactions with no entries in the dpp_execution_processes table
479: CURSOR get_valid_transaction_csr
474:
475: TYPE ProcessCodeTab IS TABLE OF DPP_EXECUTION_PROCESSES.PROCESS_CODE%TYPE INDEX BY PLS_INTEGER;
476: process_codes ProcessCodeTab;
477:
478: --Cursor to fetch those transactions with no entries in the dpp_execution_processes table
479: CURSOR get_valid_transaction_csr
480: IS
481: SELECT transaction_header_id
482: FROM dpp_transaction_headers_all dpp
481: SELECT transaction_header_id
482: FROM dpp_transaction_headers_all dpp
483: WHERE NOT EXISTS
484: (SELECT dep.transaction_header_id
485: FROM dpp_execution_processes dep
486: WHERE dep.transaction_header_id = dpp.transaction_header_id);
487:
488: --Cursor to retrieve the supplier trade profile id and org_id
489: CURSOR get_supp_trd_prfl_csr (p_transaction_header_id NUMBER)
509: IS
510: SELECT dppl.lookup_code
511: FROM dpp_lookups dppl,
512: ozf_process_setup_all opsa
513: WHERE dppl.lookup_type = 'DPP_EXECUTION_PROCESSES'
514: AND dppl.tag is not null
515: AND nvl(opsa.supp_trade_profile_id,0) = nvl(p_supp_trd_prf_id,0)
516: AND opsa.enabled_flag = 'Y'
517: AND opsa.org_id = p_org_id
583: BEGIN
584: OPEN get_process_codes_csr(l_supp_trade_profile_id,l_org_id);
585: FETCH get_process_codes_csr BULK COLLECT INTO process_codes;
586: FORALL idx IN 1..process_codes.COUNT
587: --Insert the Process codes into the DPP_EXECUTION_PROCESSES table
588: INSERT INTO DPP_EXECUTION_PROCESSES (process_code,
589: transaction_header_id,
590: created_by,
591: creation_date,
584: OPEN get_process_codes_csr(l_supp_trade_profile_id,l_org_id);
585: FETCH get_process_codes_csr BULK COLLECT INTO process_codes;
586: FORALL idx IN 1..process_codes.COUNT
587: --Insert the Process codes into the DPP_EXECUTION_PROCESSES table
588: INSERT INTO DPP_EXECUTION_PROCESSES (process_code,
589: transaction_header_id,
590: created_by,
591: creation_date,
592: last_updated_by,
603: );
604: CLOSE get_process_codes_csr;
605: EXCEPTION
606: WHEN OTHERS THEN
607: fnd_file.put_line(fnd_file.log,'Exception while fetching the process code and inserting into DPP_EXECUTION_PROCESSES: ' || SQLERRM);
608: fnd_file.new_line(fnd_file.log);
609: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
610: END;
611: END LOOP;