jl_br_ar_occ_docs_wrt_n_abtmt2
AFTER INSERT OR UPDATE OF STATUS ON AR_ADJUSTMENTS_ALL
FOR EACH ROW
Type
AFTER EACH ROW
Event
INSERT OR UPDATE
Column
When
((sys_context('JG','JGZZ_COUNTRY_CODE') in ('BR')) OR
(to_char(new.org_id) <> nvl(sys_context('JG','JGZZ_ORG_ID'),'XX')))
AND new.status = 'A'
Referencing
REFERENCING NEW AS NEW OLD AS OLD
Body
DECLARE
X_document_count NUMBER;
X_format_flag VARCHAR2(01);
X_amount_due_remaining ar_payment_schedules_all.amount_due_remaining%TYPE;
X_amount_due_original ar_payment_schedules_all.amount_due_original%TYPE;
X_occurrence_id jl_br_ar_occurrence_docs_all.occurrence_id%TYPE;
X_document_id jl_br_ar_collection_docs_all.document_id%TYPE;
X_bordero_id jl_br_ar_collection_docs_all.bordero_id%TYPE;
--X_bank_number jl_br_ar_occurrence_docs_all.bank_number%TYPE;
X_bank_party_id jl_br_ar_occurrence_docs_all.bank_party_id%TYPE;
X_bank_occurrence_type jl_br_ar_occurrence_docs_all.bank_occurrence_type%TYPE;
X_bank_occurrence_code jl_br_ar_occurrence_docs_all.bank_occurrence_code%TYPE;
X_bank_occurrence_code1 jl_br_ar_occurrence_docs_all.bank_occurrence_code%TYPE;
X_bank_occurrence_exists BOOLEAN := TRUE;
X_remittance_media jl_br_ar_occurrence_docs_all.remittance_media%TYPE;
X_std_occurrence_code jl_br_ar_bank_occurrences.std_occurrence_code%TYPE;
X_bordero_type jl_br_ar_borderos_all.bordero_type%TYPE;
X_endorsement_credit_ccid NUMBER := NULL;
X_endorsement_debit_ccid NUMBER := NULL;
X_endorsement_debit_amount NUMBER := NULL;
X_endorsement_credit_amount NUMBER := NULL;
l_ps_rec ar_payment_schedules%ROWTYPE;
l_country_code VARCHAR2(100);
BEGIN
IF (to_char(:new.org_id) <> nvl(sys_context('JG','JGZZ_ORG_ID'),'XX')) THEN
l_country_code := JG_ZZ_SHARED_PKG.GET_COUNTRY(:new.org_id,NULL,NULL);
JG_CONTEXT.name_value('JGZZ_COUNTRY_CODE',l_country_code);
JG_CONTEXT.name_value('JGZZ_ORG_ID',to_char(:new.org_id));
END IF;
IF (sys_context('JG','JGZZ_COUNTRY_CODE') = 'BR') THEN
IF NVL(:new.amount,0) < 0
THEN
SELECT global_attribute11,
amount_due_remaining,
amount_due_original
INTO X_format_flag,
X_amount_due_remaining,
X_amount_due_original
FROM ar_payment_schedules
WHERE payment_schedule_id = :new.payment_schedule_id;
IF X_format_flag = 'Y'
THEN
BEGIN
SELECT doc.document_id,
doc.bordero_id,
bor.bordero_type
INTO X_document_id,
X_bordero_id,
X_bordero_type
FROM jl_br_ar_collection_docs doc,
jl_br_ar_borderos_all bor
WHERE doc.payment_schedule_id = :new.payment_schedule_id
AND bor.bordero_id = doc.bordero_id
AND doc.document_status not in
('CANCELED','REFUSED','TOTALLY_RECEIVED','PARTIALLY_RECEIVED','WRITTEN_OFF');
EXCEPTION
WHEN NO_DATA_FOUND THEN
X_bank_occurrence_exists := FALSE;
END;
IF X_bordero_type = 'COLLECTION' THEN
IF (X_amount_due_remaining = 0)
THEN
X_std_occurrence_code := 'WRITE_OFF_REQUISITION';
ELSE
X_std_occurrence_code := 'ABATEMENT_CONCESSION';
END IF;
BEGIN
X_bank_occurrence_exists := TRUE;
/* CE uptake - Bug#2932986
SELECT boc.bank_number,
boc.bank_occurrence_code,
boc.bank_occurrence_type,
boc.remittance_media
INTO X_bank_number,
X_bank_occurrence_code,
X_bank_occurrence_type,
X_remittance_media
FROM jl_br_ar_borderos b,
jl_br_ar_bank_occurrences boc,
ap_bank_accounts_all acc,
ap_bank_branches bra
WHERE b.bank_account_id = acc.bank_account_id
AND acc.bank_branch_id = bra.bank_branch_id
AND b.bordero_id = X_bordero_id
AND boc.bank_number = bra.bank_number
AND boc.bank_occurrence_type = 'REMITTANCE_OCCURRENCE'
AND boc.std_occurrence_code = X_std_occurrence_code;
*/
SELECT boc.bank_party_id,
boc.bank_occurrence_code,
boc.bank_occurrence_type,
boc.remittance_media
INTO X_bank_party_id,
X_bank_occurrence_code,
X_bank_occurrence_type,
X_remittance_media
FROM jl_br_ar_borderos b,
jl_br_ar_bank_occurrences boc,
ce_bank_accounts acc,
ce_bank_acct_uses_all accUse,
hz_parties bank
Where b.bank_acct_use_id = accUse.bank_acct_use_id
And acc.bank_account_id = accUse.bank_account_id
And acc.BANK_ID = bank.PARTY_ID
--And bank.country = 'BR'
AND boc.bank_party_id = bank.party_id
AND b.bordero_id = X_bordero_id
AND boc.bank_occurrence_type = 'REMITTANCE_OCCURRENCE'
AND boc.std_occurrence_code = X_std_occurrence_code;
EXCEPTION
WHEN NO_DATA_FOUND THEN
X_bank_occurrence_exists := FALSE;
END;
IF X_bank_occurrence_exists
THEN
SELECT jl_br_ar_occurrence_docs_s.nextval
INTO X_occurrence_id
FROM dual;
IF X_std_occurrence_code = 'WRITE_OFF_REQUISITION'
THEN
/* CE uptake - Bug#2932986
SELECT boc.bank_occurrence_code
INTO X_bank_occurrence_code1
FROM jl_br_ar_borderos b,
jl_br_ar_bank_occurrences boc,
ap_bank_accounts_all acc,
ap_bank_branches bra
WHERE b.bank_account_id = acc.bank_account_id
AND acc.bank_branch_id = bra.bank_branch_id
AND b.bordero_id = X_bordero_id
AND boc.bank_number = bra.bank_number
AND boc.bank_occurrence_type = 'REMITTANCE_OCCURRENCE'
AND boc.std_occurrence_code = 'REMITTANCE';
*/
SELECT boc.bank_occurrence_code
INTO X_bank_occurrence_code1
FROM jl_br_ar_borderos b,
jl_br_ar_bank_occurrences boc,
ce_bank_accounts acc,
ce_bank_acct_uses_all accUse,
hz_parties bank
Where b.bank_acct_use_id = accUse.bank_acct_use_id
And acc.bank_account_id = accUse.bank_account_id
And acc.BANK_ID = bank.PARTY_ID
--And bank.country = 'BR'
AND boc.bank_party_id = bank.party_id
AND b.bordero_id = X_bordero_id
AND boc.bank_occurrence_type = 'REMITTANCE_OCCURRENCE'
AND boc.std_occurrence_code = 'REMITTANCE';
SELECT endorsement_credit_ccid,
endorsement_debit_ccid,
endorsement_debit_amount,
endorsement_credit_amount
INTO X_endorsement_debit_ccid,
X_endorsement_credit_ccid,
X_endorsement_credit_amount,
X_endorsement_debit_amount
FROM jl_br_ar_occurrence_docs
WHERE document_id = X_document_id
AND bank_occurrence_code = X_bank_occurrence_code1
AND occurrence_status <> 'CANCELED';
UPDATE jl_br_ar_collection_docs
SET document_status = 'WRITTEN_OFF',
previous_doc_status = document_status
WHERE document_id = X_document_id;
/* UPDATE ar_payment_schedules
SET global_attribute11 = NULL
WHERE payment_schedule_id = :new.payment_schedule_id;
*/
/* Replaced Update by AR's table Handler. Bug # 2249731 */
arp_ps_pkg.fetch_p(:new.payment_schedule_id, l_ps_rec);
arp_ps_pkg.lock_p(:new.payment_schedule_id);
l_ps_rec.global_attribute11 := NULL;
arp_ps_pkg.update_p(l_ps_rec, :new.payment_schedule_id);
SELECT count(*)
INTO X_document_count
FROM jl_br_ar_collection_docs
WHERE bordero_id = X_bordero_id
AND document_status not in ('CANCELED', 'WRITTEN_OFF', 'REFUSED');
IF X_document_count = 0
THEN
UPDATE jl_br_ar_borderos
SET bordero_status = 'WRITE_OFF',
write_off_date = SYSDATE
WHERE bordero_id = X_bordero_id;
END IF;
END IF;
-- CE uptake - Bug#2932986 ; insert party_id instead of bank_number
INSERT INTO jl_br_ar_occurrence_docs
(
occurrence_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
document_id,
gl_date,
bank_occurrence_code,
--bank_number,
bank_party_id,
bank_occurrence_type,
occurrence_date,
occurrence_status,
original_remittance_media,
remittance_media,
document_amount,
abatement_amount,
endorsement_credit_ccid,
endorsement_debit_ccid,
endorsement_debit_amount,
endorsement_credit_amount,
org_id
)
VALUES
(
X_occurrence_id,
SYSDATE,
:new.last_updated_by,
:new.last_update_login,
SYSDATE,
:new.created_by,
X_document_id,
sysdate,
X_bank_occurrence_code,
--X_bank_number,
X_bank_party_id,
X_bank_occurrence_type,
SYSDATE,
'CREATED',
X_remittance_media,
X_remittance_media,
X_amount_due_original,
:new.amount * -1,
X_endorsement_credit_ccid,
X_endorsement_debit_ccid,
X_endorsement_debit_amount,
X_endorsement_credit_amount,
:new.org_id
);
END IF;
END IF;
END IF;
END IF;
END IF;
END;
[Home] [Help]