Previous new Org Periods if needed to close

Previous new Org Periods if needed to close  :


exec apps.mpfl_OldPeriod_cancelation();


CREATE OR REPLACE procedure apps.mpfl_OldPeriod_cancelation
as

    l_verify_flag char(1);
    l_error_message varchar2(3000);


    cursor h_ORGANIZATION is
   
 select distinct o.ORGANIZATION_CODE,o.ORGANIZATION_ID, o.ORGANIZATION_NAME from org_acct_periods p, org_organization_definitions o
where p.OPEN_FLAG='Y'
and p.ORGANIZATION_ID=o.ORGANIZATION_ID
AND trunc(SCHEDULE_CLOSE_DATE) <TRUNC(ADD_MONTHS(sysdate,-1))

    ;


cursor l_ACCT_PERIOD_ID (ORGANIZATION_ID varchar2) is
   
  SELECT * FROM org_acct_periods p WHERE p.ORGANIZATION_ID=ORGANIZATION_ID AND trunc(SCHEDULE_CLOSE_DATE) <TRUNC(ADD_MONTHS(sysdate,-1)) and p.OPEN_FLAG='Y'
order by ACCT_PERIOD_ID asc

;


begin

for h1 in h_ORGANIZATION loop

    l_verify_flag := 'Y';
    l_error_message := null;


begin

for l1 in l_ACCT_PERIOD_ID (h1.ORGANIZATION_ID) loop

begin



update org_acct_periods p set OPEN_FLAG='N', PERIOD_CLOSE_DATE=to_char(to_date(sysdate,'dd-mon-yyyy')), SUMMARIZED_FLAG='Y'
where  ACCT_PERIOD_ID=l1.ACCT_PERIOD_ID;

commit;

exception
when others then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Data not migrated into ap_invoices_interface table...' ;

end;
end loop;
end;
end loop;
end;

Aggregate Function for Payment with multiple row descriptions of Invoice in One column

CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),

  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
     RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER
);
/
SHOW ERRORS


CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := t_string_agg(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || ',' || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/
SHOW ERRORS


CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
SHOW ERRORS

The aggregate function is implemented using a type and type body, and is used within a query.

COLUMN employees FORMAT A50

select gjh.JE_CATEGORY,gjh.JE_SOURCE,gjh.JE_HEADER_ID,gjl.JE_LINE_NUM,gjl.CODE_COMBINATION_ID,
gcc.SEGMENT1||'-'||gcc.SEGMENT2||'-'||gcc.SEGMENT3||'-'||gcc.segment4||'-'||gcc.segment5||'-'||gcc.segment6 ACCOUNT,ffvt.DESCRIPTION,
xal.ACCOUNTED_DR,xal.ACCOUNTED_CR,gir.GL_SL_LINK_ID,
gir.REFERENCE_5,gir.REFERENCE_6,gir.REFERENCE_7,xte.SOURCE_ID_INT_1,to_char(aca.CHECK_NUMBER) I,to_char(aca.ORG_ID) N--,aia.DESCRIPTION
,K.inv_desc
from gl_je_headers gjh, gl_je_lines gjl,gl_code_combinations gcc,gl_import_references gir,xla_ae_lines xal,xla.xla_transaction_entities xte,ap_checks_all aca
,FND_FLEX_VALUES ffv,FND_FLEX_VALUES_TL ffvt--,ap_invoice_payments_all aipa, ap_invoices_all aia 
,(SELECT check_id, NVL(string_agg(aia.description),'.') AS inv_desc
FROM    ap_invoice_payments_all aipa, ap_invoices_all aia where --check_id=661083 and
aipa.INVOICE_ID=aia.INVOICE_ID
GROUP BY check_id) k
where gjh.STATUS = 'P' and gjh.PERIOD_NAME = 'Dec-16-17' and gjh.JE_SOURCE = 'Payables' and gjh.JE_CATEGORY = 'Payments'
and gjh.JE_HEADER_ID = gjl.JE_HEADER_ID and #.M=t.M --and gjl.CODE_COMBINATION_ID = 2509 --12657 --= '97430'        
and   gjl.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID --and gcc.segment1 = '103' --and gcc.segment6 = '214551'
and ffv.FLEX_VALUE_SET_ID = 1014876  and gcc.segment6 = ffv.FLEX_VALUE and ffv.FLEX_VALUE_ID = ffvt.FLEX_VALUE_ID
and gjl.JE_HEADER_ID = gir.JE_HEADER_ID and gjl.JE_LINE_NUM = gir.JE_LINE_NUM and   gir.GL_SL_LINK_ID = xal.GL_SL_LINK_ID
and   gir.REFERENCE_5  = xte.ENTITY_ID  and xte.SOURCE_ID_INT_1 = aca.CHECK_ID and aca.ORG_ID = 122 --and aca.CHECK_ID IN (658686,512060, 661083)
and aca.CHECK_ID=k.CHECK_ID 
--and   aca.CHECK_ID = aipa.CHECK_ID and aipa.INVOICE_ID = aia.INVOICE_ID
order by xte.SOURCE_ID_INT_1