[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT unique apid.invoice_id
FROM
ap_invoice_distributions_all apid,
po_distributions_all pod,
po_headers_all phh,
igc_cc_headers cch
WHERE
apid.po_distribution_id = pod.po_distribution_id AND
pod.po_header_id = phh.po_header_id AND
phh.org_id = cch.org_id AND
phh.type_lookup_code = 'STANDARD' AND
phh.segment1 = cch.cc_num AND
cch.cc_header_id = p_cc_header_id;
SELECT cancelled_date
INTO l_cancelled_date
FROM ap_invoices_all
WHERE invoice_id = l_invoice_id;
SELECT count(invoice_distribution_id)
INTO l_dist_count
FROM
ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id;
SELECT count(invoice_distribution_id)
INTO l_appr_dist_count
FROM
ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id AND
NVL(match_status_flag,'X') = 'A' AND
NVL(exchange_rate_variance,0) = 0;
SELECT count(invoice_distribution_id)
INTO l_appr_dist_count
FROM
ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id AND
NVL(match_status_flag, 'X') = 'A' ;
SELECT *
FROM IGC_CC_HEADERS
WHERE
CC_HEADER_ID = p_CC_HEADER_ID
FOR UPDATE
NOWAIT;
SELECT 'Y'
FROM PO_HEADERS_ALL A
WHERE
A.PO_HEADER_ID = (SELECT C.PO_HEADER_ID
FROM IGC_CC_HEADERS B,
PO_HEADERS_ALL C
WHERE B.ORG_ID = C.ORG_ID AND
B.CC_NUM = C.SEGMENT1 AND
C.TYPE_LOOKUP_CODE = 'STANDARD' AND
B.CC_HEADER_ID = P_CC_HEADER_ID);
SELECT 'Y'
FROM PO_HEADERS_ALL A
WHERE
A.PO_HEADER_ID = (SELECT C.PO_HEADER_ID
FROM IGC_CC_HEADERS B,
PO_HEADERS_ALL C
WHERE B.ORG_ID = C.ORG_ID AND
B.CC_NUM = C.SEGMENT1 AND
C.TYPE_LOOKUP_CODE = 'STANDARD' AND
B.CC_HEADER_ID = P_CC_HEADER_ID)
FOR UPDATE
NOWAIT;
SELECT *
FROM IGC_CC_HEADERS
WHERE IGC_CC_HEADERS.CC_HEADER_ID = p_CC_HEADER_ID;
SELECT *
FROM IGC_CC_ACCT_LINES
WHERE IGC_CC_ACCT_LINES.CC_HEADER_ID = HEADER_ID;
SELECT *
FROM IGC_CC_DET_PF_V
WHERE IGC_CC_DET_PF_V.CC_ACCT_LINE_ID = ACCT_LINE_ID;
SELECT *
FROM IGC_CC_HEADERS
WHERE PARENT_HEADER_ID IN ( SELECT A.PARENT_HEADER_ID
FROM IGC_CC_HEADERS A , IGC_CC_HEADERS B
WHERE A.PARENT_HEADER_ID = B.CC_HEADER_ID
AND A.PARENT_HEADER_ID = p_CC_HEADER_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (p_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (
p_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
p_CC_HEADER_ID,
V5.CC_ACCT_LINE_ID,
V5.CC_DET_PF_LINE_ID,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
p_CC_HEADER_ID,
V5.CC_ACCT_LINE_ID,
V5.CC_DET_PF_LINE_ID,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
p_CC_HEADER_ID,
V5.CC_ACCT_LINE_ID,
V5.CC_DET_PF_LINE_ID,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
/*************** Checking Release Validation for each selected Cover CC *****/
OPEN C9;
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V9.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V9.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V9.CC_HEADER_ID,
NULL,
NULL,
'Contract Commitment has Revaluation Variances',
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V9.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'F'
WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'P'
WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
UPDATE IGC_CC_HEADERS
SET CC_APPRVL_STATUS = 'IP'
WHERE IGC_CC_HEADERS.CC_HEADER_ID = V9.CC_HEADER_ID;
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'P'
WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V9.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'F'
WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V9.CC_HEADER_ID,
V5.CC_ACCT_LINE_ID,
V5.CC_DET_PF_LINE_ID,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'F'
WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'P'
WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
UPDATE IGC_CC_HEADERS
SET CC_APPRVL_STATUS = 'IP'
WHERE IGC_CC_HEADERS.CC_HEADER_ID = V9.CC_HEADER_ID;
SELECT CC_STATE,CC_APPRVL_STATUS
INTO l_STATE,l_PREVIOUS_APPRVL_STATUS
FROM IGC_CC_HEADERS
WHERE CC_HEADER_ID = V9.CC_HEADER_ID;
SELECT 'Y'
INTO l_DUMMY
FROM PO_HEADERS_ALL A
WHERE
A.PO_HEADER_ID =
(SELECT C.PO_HEADER_ID
FROM IGC_CC_HEADERS B,
PO_HEADERS_ALL C
WHERE B.ORG_ID = C.ORG_ID AND
B.CC_NUM = C.SEGMENT1 AND
C.TYPE_LOOKUP_CODE = 'STANDARD' AND
B.CC_HEADER_ID = V9.CC_HEADER_ID );
UPDATE PO_HEADERS_ALL
SET APPROVED_FLAG = 'N'
WHERE (SEGMENT1,ORG_ID,TYPE_LOOKUP_CODE) IN
(SELECT SEGMENT1,a.ORG_ID,TYPE_LOOKUP_CODE
FROM PO_HEADERS_ALL a, IGC_CC_HEADERS b
WHERE a.SEGMENT1 = b.CC_NUM
AND a.ORG_ID = b.ORG_ID
AND a.TYPE_LOOKUP_CODE = 'STANDARD'
AND b.CC_HEADER_ID = V9.CC_HEADER_ID);
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'P'
WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V9.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'F'
WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V9.CC_HEADER_ID,
V5.CC_ACCT_LINE_ID,
V5.CC_DET_PF_LINE_ID,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'F'
WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'P'
WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
UPDATE IGC_CC_HEADERS
SET CC_APPRVL_STATUS = 'IP'
WHERE IGC_CC_HEADERS.CC_HEADER_ID = V9.CC_HEADER_ID;
SELECT CC_STATE,CC_APPRVL_STATUS
INTO l_STATE,l_PREVIOUS_APPRVL_STATUS
FROM IGC_CC_HEADERS
WHERE CC_HEADER_ID = V9.CC_HEADER_ID;
SELECT 'Y'
INTO l_DUMMY
FROM PO_HEADERS_ALL A
WHERE
A.PO_HEADER_ID =
(SELECT C.PO_HEADER_ID
FROM IGC_CC_HEADERS B,
PO_HEADERS_ALL C
WHERE B.ORG_ID = C.ORG_ID AND
B.CC_NUM = C.SEGMENT1 AND
C.TYPE_LOOKUP_CODE = 'STANDARD' AND
B.CC_HEADER_ID = V9.CC_HEADER_ID );
UPDATE PO_HEADERS_ALL
SET APPROVED_FLAG = 'N'
WHERE (SEGMENT1,ORG_ID,TYPE_LOOKUP_CODE) IN
(SELECT SEGMENT1,a.ORG_ID,TYPE_LOOKUP_CODE
FROM PO_HEADERS_ALL a, IGC_CC_HEADERS b
WHERE a.SEGMENT1 = b.CC_NUM
AND a.ORG_ID = b.ORG_ID
AND a.TYPE_LOOKUP_CODE = 'STANDARD'
AND b.CC_HEADER_ID = V9.CC_HEADER_ID);
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'P'
WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V9.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'F'
WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V9.CC_HEADER_ID,
V5.CC_ACCT_LINE_ID,
V5.CC_DET_PF_LINE_ID,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'F'
WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'P'
WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
UPDATE IGC_CC_HEADERS
SET CC_APPRVL_STATUS = 'IP'
WHERE IGC_CC_HEADERS.CC_HEADER_ID = V9.CC_HEADER_ID;
SELECT CC_STATE,CC_APPRVL_STATUS
INTO l_STATE,l_PREVIOUS_APPRVL_STATUS
FROM IGC_CC_HEADERS
WHERE CC_HEADER_ID = V9.CC_HEADER_ID;
SELECT 'Y'
INTO l_DUMMY
FROM PO_HEADERS_ALL A
WHERE
A.PO_HEADER_ID =
(SELECT C.PO_HEADER_ID
FROM IGC_CC_HEADERS B,
PO_HEADERS_ALL C
WHERE B.ORG_ID = C.ORG_ID AND
B.CC_NUM = C.SEGMENT1 AND
C.TYPE_LOOKUP_CODE = 'STANDARD' AND
B.CC_HEADER_ID = V9.CC_HEADER_ID );
UPDATE PO_HEADERS_ALL
SET APPROVED_FLAG = 'N'
WHERE (SEGMENT1,ORG_ID,TYPE_LOOKUP_CODE) IN
(SELECT SEGMENT1,a.ORG_ID,TYPE_LOOKUP_CODE
FROM PO_HEADERS_ALL a, IGC_CC_HEADERS b
WHERE a.SEGMENT1 = b.CC_NUM
AND a.ORG_ID = b.ORG_ID
AND a.TYPE_LOOKUP_CODE = 'STANDARD'
AND b.CC_HEADER_ID = V9.CC_HEADER_ID);
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'P'
WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'F'
WHERE A.CC_HEADER_ID = V3.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'F'
WHERE A.CC_HEADER_ID = V3.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'F'
WHERE A.CC_HEADER_ID = V3.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE ,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES (P_PROCESS_TYPE,
p_PROCESS_PHASE,
V3.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V3.ORG_ID,
V3.SET_OF_BOOKS_ID,
p_REQUEST_ID);
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'F'
WHERE A.CC_HEADER_ID = V3.CC_HEADER_ID
AND A.REQUEST_ID = p_REQUEST_ID
AND A.PROCESS_TYPE = p_PROCESS_TYPE
AND A.ORG_ID = p_ORG_ID
AND A.SET_OF_BOOKS_ID = p_SOB_ID;
SELECT pod.po_distribution_id
FROM
po_distributions_all pod,
po_headers_all phh,
igc_cc_headers cch
WHERE
pod.po_header_id = phh.po_header_id AND
phh.org_id = cch.org_id AND
phh.type_lookup_code = 'STANDARD' AND
phh.segment1 = cch.cc_num AND
cch.cc_header_id = p_cc_header_id;
SELECT unique api.invoice_id
FROM
ap_invoice_distributions_all apid,
ap_invoices_all api
WHERE
apid.invoice_id = api.invoice_id AND
apid.po_distribution_id = p_po_distribution_id;
SELECT cancelled_date , NVL(payment_status_flag,'N')
INTO l_cancelled_date, l_payment_status_flag
FROM ap_invoices_all
WHERE invoice_id = l_invoice_id;
SELECT SUM(NVL(apid.amount,0))
INTO l_inv_dist_total
FROM
ap_invoice_distributions_all apid
WHERE
apid.invoice_id = l_invoice_id AND
apid.po_distribution_id = l_po_distribution_id ;
/* Inserts row into budgetary control interface table */
PROCEDURE Insert_Interface_Row(p_cc_interface_rec IN igc_cc_interface%ROWTYPE, l_insert_return_status OUT NOCOPY VARCHAR2)
IS
BEGIN
INSERT INTO igc_cc_interface (
batch_line_num,
cc_header_id,
cc_version_num,
cc_acct_line_id,
cc_det_pf_line_id,
set_of_books_id,
code_combination_id,
cc_transaction_date,
transaction_description,
encumbrance_type_id,
currency_code,
cc_func_dr_amt,
cc_func_cr_amt,
je_source_name,
je_category_name,
actual_flag,
budget_dest_flag,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
period_set_name,
period_name,
cbc_result_code,
status_code,
budget_version_id,
budget_amt,
commitment_encmbrnc_amt,
obligation_encmbrnc_amt,
funds_available_amt,
document_type,
reference_1,
reference_2,
reference_3,
reference_4,
reference_5,
reference_6,
reference_7,
reference_8,
reference_9,
reference_10,
cc_encmbrnc_date,
/*Bug No : 6341012. SLA Uptake. Project_Line field added*/
project_line
)
VALUES
(p_cc_interface_rec.batch_line_num,
p_cc_interface_rec.cc_header_id,
p_cc_interface_rec.cc_version_num,
p_cc_interface_rec.cc_acct_line_id,
p_cc_interface_rec.cc_det_pf_line_id,
p_cc_interface_rec.set_of_books_id,
p_cc_interface_rec.code_combination_id,
p_cc_interface_rec.cc_transaction_date,
p_cc_interface_rec.transaction_description,
p_cc_interface_rec.encumbrance_type_id,
p_cc_interface_rec.currency_code,
p_cc_interface_rec.cc_func_dr_amt,
p_cc_interface_rec.cc_func_cr_amt,
p_cc_interface_rec.je_source_name,
p_cc_interface_rec.je_category_name,
p_cc_interface_rec.actual_flag,
p_cc_interface_rec.budget_dest_flag,
p_cc_interface_rec.last_update_date,
p_cc_interface_rec.last_updated_by,
p_cc_interface_rec.last_update_login,
p_cc_interface_rec.creation_date,
p_cc_interface_rec.created_by,
p_cc_interface_rec.period_set_name,
p_cc_interface_rec.period_name,
p_cc_interface_rec.cbc_result_code,
p_cc_interface_rec.status_code,
p_cc_interface_rec.budget_version_id,
p_cc_interface_rec.budget_amt,
p_cc_interface_rec.commitment_encmbrnc_amt,
p_cc_interface_rec.obligation_encmbrnc_amt,
p_cc_interface_rec.funds_available_amt,
p_cc_interface_rec.document_type,
p_cc_interface_rec.reference_1,
p_cc_interface_rec.reference_2,
p_cc_interface_rec.reference_3,
p_cc_interface_rec.reference_4,
p_cc_interface_rec.reference_5,
p_cc_interface_rec.reference_6,
p_cc_interface_rec.reference_7,
p_cc_interface_rec.reference_8,
p_cc_interface_rec.reference_9,
p_cc_interface_rec.reference_10,
p_cc_interface_rec.cc_encmbrnc_date,
/*Bug No : 6341012. SLA Uptake. Project_Line field added*/
p_cc_interface_rec.project_line
);
l_insert_return_status := 'F';
FND_LOG.STRING(l_state_level, 'igc_cc_rep_yep_pvt.insert_interface_row',
'Record not inserted' || to_char(sysdate,'DD-MON-YY:MI:SS'));
END Insert_Interface_Row;
l_insert_status OUT NOCOPY VARCHAR2
)
IS
l_cc_interface_rec igc_cc_interface%ROWTYPE;
l_insert_return_status VARCHAR2(1);
SELECT cc_num,cc_version_num, set_of_books_id, cc_acct_date
INTO l_cover_cc_num, l_cover_cc_version_num, l_cover_set_of_books_id, l_cover_cc_acct_date
FROM igc_cc_headers
WHERE cc_header_id = p_cc_headers_rec.parent_header_id;
SELECT cc_acct_desc, cc_budget_code_combination_id
INTO l_cover_cc_acct_desc, l_cover_budg_code_comb_id
FROM igc_cc_acct_lines
WHERE cc_acct_line_id = p_cc_acct_lines_rec.parent_acct_line_id;
l_cc_interface_rec.last_update_date := sysdate;
l_cc_interface_rec.last_updated_by := -1;
l_cc_interface_rec.last_update_login := -1;
SELECT cc_acct_func_amt , cc_acct_func_billed_amt
INTO l_func_amt, l_func_billed_amt
FROM igc_cc_acct_lines_v
WHERE cc_acct_line_id = p_cc_acct_lines_rec.cc_acct_line_id;
SELECT Nvl(cc_acct_func_amt,0) , Nvl(IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( ccal.cc_acct_line_id),0)
INTO l_func_amt, l_func_billed_amt
FROM igc_cc_acct_lines ccal
WHERE ccal.cc_acct_line_id = p_cc_acct_lines_rec.cc_acct_line_id;
Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
IF l_insert_return_status = 'F' THEN
raise E_RETURN_FAIL;
Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
IF l_insert_return_status = 'F' THEN
raise E_RETURN_FAIL;
SELECT SUM(NVL(igcci.cc_func_dr_amt,0))
INTO l_dr_tran_amount
FROM igc_cc_interface igcci
WHERE
igcci.cc_header_id = l_cc_header_id AND
igcci.actual_flag = 'E' AND
igcci.cc_det_pf_line_id IN (SELECT ccdpf.cc_det_pf_line_id
FROM igc_cc_det_pf ccdpf
WHERE cc_acct_line_id =
l_cc_acct_line_id);
SELECT SUM(NVL(igcci.cc_func_cr_amt,0))
INTO l_cr_tran_amount
FROM igc_cc_interface igcci
WHERE
igcci.cc_header_id = l_cc_header_id AND
igcci.actual_flag = 'E' AND
igcci.cc_det_pf_line_id IN (SELECT ccdpf.cc_det_pf_line_id
FROM igc_cc_det_pf ccdpf
WHERE cc_acct_line_id =
l_cc_acct_line_id);
Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
IF l_insert_return_status = 'F' THEN
raise E_RETURN_FAIL;
Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
IF l_insert_return_status = 'F' THEN
raise E_RETURN_FAIL;
SELECT SUM(NVL(igcci.cc_func_dr_amt,0))
INTO l_tran_amount
FROM igc_cc_interface igcci
WHERE
igcci.cc_header_id = p_cc_headers_rec.cc_header_id AND
igcci.actual_flag = 'E' AND
cc_det_pf_line_id IN (SELECT ccdpf.cc_det_pf_line_id
FROM igc_cc_det_pf ccdpf
WHERE cc_acct_line_id =
p_cc_acct_lines_rec.cc_acct_line_id);
/* 6670549 The Select statement changed as we are entering debit amount as negative instead of positive credit amount
SELECT SUM(NVL(igcci.cc_func_cr_amt,0)) */
SELECT SUM(NVL(igcci.cc_func_dr_amt,0))
INTO l_tran_amount
FROM igc_cc_interface igcci
WHERE
igcci.cc_header_id = p_cc_headers_rec.cc_header_id AND
igcci.actual_flag = 'E' AND
cc_det_pf_line_id IN (SELECT ccdpf.cc_det_pf_line_id
FROM igc_cc_det_pf ccdpf
WHERE cc_acct_line_id =
p_cc_acct_lines_rec.cc_acct_line_id);
Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
IF l_insert_return_status = 'F' THEN
raise E_RETURN_FAIL;
Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
IF l_insert_return_status = 'F' THEN
raise E_RETURN_FAIL;
SELECT cc_det_pf_date
INTO l_cover_cc_det_pf_date
FROM igc_cc_det_pf
WHERE cc_det_pf_line_id = p_cc_pmt_fcst_rec.parent_det_pf_line_id;
SELECT cc_det_pf_func_amt,cc_det_pf_func_billed_amt
INTO l_func_amt,l_func_billed_amt
FROM igc_cc_det_pf_v
WHERE cc_det_pf_line_id = p_cc_pmt_fcst_rec.cc_det_pf_line_id;
Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
IF l_insert_return_status = 'F' THEN
raise E_RETURN_FAIL;
Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
IF l_insert_return_status = 'F' THEN
raise E_RETURN_FAIL;
SELECT cc_det_pf_entered_amt,
cc_det_pf_func_amt,
cc_det_pf_billed_amt,
cc_det_pf_func_billed_amt
INTO l_ent_amt,
l_func_amt,
l_billed_amt,
l_func_billed_amt
FROM igc_cc_det_pf_v
WHERE cc_det_pf_line_id = p_cc_pmt_fcst_rec.cc_det_pf_line_id;
Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
IF l_insert_return_status = 'F' THEN
raise E_RETURN_FAIL;
Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
IF l_insert_return_status = 'F' THEN
raise E_RETURN_FAIL;
SELECT cc_det_pf_func_billed_amt
INTO l_func_billed_amt
FROM igc_cc_det_pf_v
WHERE cc_det_pf_line_id = p_cc_pmt_fcst_rec.cc_det_pf_line_id;
Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
IF l_insert_return_status = 'F' THEN
raise E_RETURN_FAIL;
Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
IF l_insert_return_status = 'F' THEN
raise E_RETURN_FAIL;
l_insert_status := 'F';
l_insert_status VARCHAR2(1);
e_delete EXCEPTION;
SELECT *
FROM igc_cc_det_pf
WHERE cc_acct_line_id = t_cc_acct_line_id;
SELECT *
FROM igc_cc_acct_lines ccac
WHERE ccac.cc_header_id = t_cc_header_id;
SELECT *
INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = p_cc_header_id;
SELECT set_of_books_id
INTO l_cover_set_of_books_id
FROM igc_cc_headers
WHERE cc_header_id = l_cc_headers_rec.parent_header_id;
/* Delete existing interface rows */
IF (l_cc_headers_rec.cc_type = 'R')
THEN
BEGIN
DELETE igc_cc_interface
WHERE cc_header_id = l_cc_headers_rec.parent_header_id AND
actual_flag = 'E';
DELETE igc_cc_interface
WHERE cc_header_id = p_cc_header_id AND
actual_flag = 'E';
DELETE igc_cc_interface
WHERE cc_header_id IN (SELECT cc_header_id
FROM igc_cc_headers
WHERE parent_header_id = p_cc_header_id )
AND actual_flag = 'E';
l_insert_status
);
l_insert_status
);
IF l_insert_status = 'F' THEN
RETURN ('F');
l_insert_status
);
l_insert_status
);
IF l_insert_status = 'F' and p_process_type = 'R' THEN
RETURN('F');
SELECT count(*)
INTO l_interface_row_count
FROM igc_cc_interface
WHERE cc_header_id = l_cc_headers_rec.parent_header_id;
SELECT count(*)
INTO l_interface_row_count
FROM igc_cc_interface
WHERE cc_header_id = p_cc_header_id;
SELECT NVL(enable_budgetary_control_flag,'N'),currency_code
INTO l_enable_budg_control_flag,l_currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = p_sob_id;
SELECT req_encumbrance_flag, purch_encumbrance_flag
INTO l_req_encumbrance_flag, l_purch_encumbrance_flag
FROM financials_system_params_all
WHERE set_of_books_id = p_sob_id AND
org_id = p_org_id;
SELECT cc_bc_enable_flag
INTO l_cc_bc_enable_flag
FROM igc_cc_bc_enable
WHERE set_of_books_id = p_sob_id;