1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132
| EXECUTE immediate 'INSERT into EXTRAC_REGL_' || BASE || '_GEN_NON_SOLD (
select
AR_CASH_RECEIPTS_ALL.ORG_ID ID_SOC_JUR,
HR_ALL_ORGANIZATION_UNITS.NAME SOC_JUR,
AR_CASH_RECEIPTS_ALL.RECEIPT_NUMBER NUM_RGL,
AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID ID_RGL,
AR_CASH_RECEIPTS_ALL.DOC_SEQUENCE_VALUE NUM_DOC_RGL,
to_char(AR_CASH_RECEIPTS_ALL.RECEIPT_DATE, ''DD-MM-YYYY'') DATE_RGL,
to_char(AR_PAYMENT_SCHEDULES_ALL.DUE_DATE, ''DD-MM-YYYY'') DATE_ECH_RGL,
DECODE(''' || PARAM_DATE_GL ||
''',NULL,to_char(AR_CASH_RECEIPT_HISTORY_ALL.GL_DATE,''DD-MM-YYYY''),''' ||
to_char(to_date(to_char(PARAM_DATE_GL, 'DD-MON-YYYY'), 'DD-MON-YYYY'), 'DD-MM-YYYY') || ''') DATE_COMPTABLE,
' || PARAM_EXERCICE_INTEG || ' EXERCICE_INTEGR,
AR_CASH_RECEIPTS_ALL.CURRENCY_CODE DEVIsE_RGL,
HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER CLIENT_COMPTABLE,
HZ_CUST_SITE_USES_ALL.LOCATION CLIENT_FACTURE,
AR_RECEIPT_METHODS.NAME MODE_PMT,
AR_CASH_RECEIPTS_ALL.COMMENTS COMM_RGL,
AP_BANK_BRANCHES.BANK_NUMBER CODE_BANQUE,
AP_BANK_BRANCHES.BANK_NUM CODE_AG_BANQUE,
AP_BANK_ACCOUNTS_ALL.BANK_ACCOUNT_NUM NUM_CPTE_BANQUE,
substr(HZ_CUST_SITE_USES_ALL.LOCATION, 1, 3) CODE_AGENCE,
decode(CC__SOCIETE.CD_CPTA_BASE,
1,
''HOLDING'',
2,
''EIDF'',
3,
''ECEM'',
4,
''ENE'',
5,
''EMO'',
6,
''ECO'') REGION,
AR_CASH_RECEIPTS_ALL.AMOUNT MNT_RGL,
a.MNT_LT_RGL,
a.MNT_NLT_RGL,
a.MNT_CPTE_RGL,
a.MNT_NID_RGL
from AR_CASH_RECEIPTS_ALL,
GL_SETS_OF_BOOKS,
CC__SOCIETE,
HR_ALL_ORGANIZATION_UNITS,
AR_PAYMENT_SCHEDULES_ALL,
AR_RECEIPT_METHODS,
AR_CASH_RECEIPT_HISTORY_ALL,
HZ_CUST_ACCOUNTS,
HZ_CUST_SITE_USES_ALL,
AP_BANK_ACCOUNTS_ALL,
AP_BANK_BRANCHES,
(select ARAA.Cash_Receipt_Id, ltrim(to_char(sum(DECODE(ARAA.STATUS,
''APP'',
DECODE(ARAA.CONFIRMED_FLAG,
''N'',
0,
NVL(NVL(ARAA.AMOUNT_APPLIED_FROM,
ARAA.AMOUNT_APPLIED),
0)),
''ACTIVITY'',
DECODE(ARAA.APPLIED_PAYMENT_SCHEDULE_ID,
-2,
NVL(NVL(ARAA.AMOUNT_APPLIED_FROM,
ARAA.AMOUNT_APPLIED),
0),
-3,
NVL(NVL(ARAA.AMOUNT_APPLIED_FROM,
ARAA.AMOUNT_APPLIED),
0),
-5,
NVL(NVL(ARAA.AMOUNT_APPLIED_FROM,
ARAA.AMOUNT_APPLIED),
0),
-6,
NVL(NVL(ARAA.AMOUNT_APPLIED_FROM,
ARAA.AMOUNT_APPLIED),
0),
0),
0)),
''999999999999999999.00'')) AS MNT_LT_RGL,
ltrim(to_char(sum(DECODE(ARAA.STATUS,
''UNAPP'',
NVL(ARAA.AMOUNT_APPLIED,
0),
0)),
''999999999999999999.00'')) AS MNT_NLT_RGL,
ltrim(to_char(sum(DECODE(ARAA.STATUS,
''ACC'',
NVL(ARAA.AMOUNT_APPLIED,
0),
0)),
''999999999999999999.00'')) AS MNT_CPTE_RGL,
ltrim(to_char(sum(DECODE(ARAA.STATUS,
''UNID'',
NVL(ARAA.AMOUNT_APPLIED,
0),
0)),
''999999999999999999.00'')) AS MNT_NID_RGL from AR_RECEIVABLE_APPLICATIONS_ALL ARAA group by ARAA.Cash_Receipt_Id ) a
WHERE AR_CASH_RECEIPTS_ALL.Org_Id = GL_SETS_OF_BOOKS.attribute7
AND CPT_FUSION_PKG.F_CPT_CONV_SOCIETE(GL_SETS_OF_BOOKS.attribute6,
''SURF'') = CC__SOCIETE.cd
AND gl_sets_of_books.attribute6 = DECODE(nvl(''' || SOCIETE ||
''',''X''), ''X'', gl_sets_of_books.attribute6, nvl(''' || SOCIETE ||
''',''X'')) -- condition sur la société
AND GL_SETS_OF_BOOKS.attribute7 =
HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID
AND gl_sets_of_books.attribute5 != ''RUB_NOV''
AND a.Cash_Receipt_Id=AR_CASH_RECEIPTS_ALL.Cash_Receipt_Id
AND AR_CASH_RECEIPTS_ALL.Cash_Receipt_Id =
AR_PAYMENT_SCHEDULES_ALL.Cash_Receipt_Id
AND AR_CASH_RECEIPTS_ALL.Receipt_Method_Id =
AR_RECEIPT_METHODS.Receipt_Method_Id
AND AR_CASH_RECEIPTS_ALL.Pay_From_Customer =
HZ_CUST_ACCOUNTS.Cust_Account_Id
AND AR_CASH_RECEIPTS_ALL.Customer_Site_Use_Id =
HZ_CUST_SITE_USES_ALL.Site_Use_Id
AND AR_CASH_RECEIPTS_ALL.remittance_bank_account_id =
AP_BANK_ACCOUNTS_ALL.Bank_Account_Id
AND AP_BANK_ACCOUNTS_ALL.BANK_BRANCH_ID =
AP_BANK_BRANCHES.BANK_BRANCH_ID
AND AR_CASH_RECEIPTS_ALL.Cash_Receipt_Id =
AR_CASH_RECEIPT_HISTORY_ALL.CASH_RECEIPT_ID(+)
AND AR_CASH_RECEIPT_HISTORY_ALL.First_Posted_Record_Flag=''Y''
AND AR_CASH_RECEIPT_HISTORY_ALL.GL_DATE < ''' || DATE_GL || '''
AND AR_CASH_RECEIPTS_ALL.Status!=''REV''
AND ( to_number(a.MNT_LT_RGL,''999999999999999999.00'') = 0 -- non lettré
OR to_number(a.MNT_NLT_RGL,''999999999999999999.00'') > 0 -- partiellement lettré
OR (to_number(a.MNT_LT_RGL,''999999999999999999.00'') = AR_CASH_RECEIPTS_ALL.AMOUNT AND AR_PAYMENT_SCHEDULES_ALL.DUE_DATE > ''' ||
DATE_GL || '''))
)'; |
Partager