Query to retrieve GL Journal Import

SELECT
       glc.segment1
       || '-'
       || apps.gl_flexfields_pkg.get_description_sql
                                                    (glc.chart_of_accounts_id,
                                                     1,
                                                     glc.segment1
                                                    ) "Company" , 
       to_char(glh.default_effective_date,'DD-MON-YYYY') "Accounting Date"
       ,glh.je_source "Source"
       ,gljc.user_je_category_name "Category"
       ,glh.doc_sequence_value "Document Number"
       ,glh.currency_code "Currency"
        ,NVL(REPLACE(gll.description,chr(9),''),'')"Description"
       ,gll.entered_dr "Dr"
       ,gll.entered_cr "Cr"
       ,glh.created_by "Created by"
       ,glh.creation_date     
  FROM apps.gl_je_headers glh,
       apps.gl_je_lines gll,
       apps.gl_je_categories_tl gljc,
       apps.gl_code_combinations glc
 WHERE glh.je_header_id = gll.je_header_id
   AND gll.code_combination_id = glc.code_combination_id
   --AND glh.je_source = 'Manual'
   AND glh.default_effective_date BETWEEN :lv_from_date AND :lv_to_date
   AND glh.je_category=gljc.je_category_name
   order by doc_sequence_value,default_effective_date,gll.entered_dr,gll.entered_cr;

Dynamic Approver List for AME

Oracle has provided an API to get the dynamic list of approvers for AME. ame_dynamic_approval_pkg.get_ame_apprs_and_ins_list.
We need to pass the transaction type id and transaction number. Rransaction Id is available in HR_API_TRANSACTION_TABLE and transaction_type_id is available in AME_CALLING_APPS table.

API Usage Code

CREATE OR REPLACE PROCEDURE GET_AME_TRANSACTION_APPROVERS

(P_AME_TRANSACTION_ID IN NUMBER,
 P_TRANSACTION_TYPE_ID IN VARCHAR2 DEFAULT ‘LSGEXLR’,
P_AME_APPROVERS_LIST OUT AME_APPROVER_RECORD2_TABLE_SS )

IS
 p_apprs_view_type varchar2(100):=’Active’;
p_coa_insertions_flag varchar2(100):= ‘N’;
v_ame_approvers_list ame_approver_record2_table_ss;
 v_ame_order_type_list ame_insertion_record2_table_ss:= ame_insertion_record2_table_ss();
 v_all_approvers_count varchar2(100);
 v_warning_msg_name varchar2(100);
 v_error_msg_text varchar2(100);

BEGIN

–Reference to get the Transaction Type ID, Note that LSG is my custom Application Name
–select * from ame_calling_apps
–where fnd_application_id = 800
–and application_name = ‘LSG External Learning Request’

v_ame_approvers_list := ame_approver_record2_table_ss();

AME_DYNAMIC_APPROVAL_PKG.GET_AME_APPRS_AND_INS_LIST

(

p_application_id => 800
 ,p_transaction_type =>P_TRANSACTION_TYPE_ID
 ,p_transaction_id => P_AME_TRANSACTION_ID
 ,p_ame_approvers_list => v_ame_approvers_list
 ,p_ame_order_type_list => v_ame_order_type_list
 ,p_all_approvers_count => v_all_approvers_count
 ,p_warning_msg_name => v_warning_msg_name
 ,p_error_msg_text => v_error_msg_text

);

P_AME_APPROVERS_LIST := v_ame_approvers_list;

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line(‘exception ‘sqlerrm);

END;

Sample procedure given below:

DECLARE

v_AME_TRANSACTION_ID NUMBER:=21610630 ;
 v_TRANSACTION_TYPE_ID VARCHAR2(20):=’LSGEXLR’;
v_AME_APPROVERS_LIST AME_APPROVER_RECORD2_TABLE_SS ;

BEGIN

GET_AME_TRANSACTION_APPROVERS

(

P_AME_TRANSACTION_ID =>v_AME_TRANSACTION_ID

,P_AME_APPROVERS_LIST =>V_AME_APPROVERS_LIST

);

IF(v_ame_approvers_list.count() ) > 0THEN

FOR i IN 1..v_ame_approvers_list.count() LOOP

dbms_output.put_line(‘ approver_order_number =>‘ v_ame_approvers_list(i).approver_order_number
‘ **** display_name =>‘v_ame_approvers_list(i).display_name
‘ **** Approver Category =>‘v_ame_approvers_list(i).approver_category
‘ **** approval_status =>‘v_ame_approvers_list(i).approval_status
‘ **** source =>‘v_ame_approvers_list(i).source );

END LOOP;

ELSE

dbms_output.put_line(‘ No Approver Found’);

END IF;

END;

Project Accounting - Useful Queries

Project Basic Query:


select proj.carrying_out_organization_name  project_organization,
  proj.segment1  project_number,
  proj.name  Project_Short_Name,
  proj.city ,
  proj.region,
  proj.long_name project_long_name,
  proj.start_date  transaction_start_date,
  proj.target_finish_date  Target_Finish_Date,
  proj.project_type ,
  proj.project_type_class_code ,
  proj.completion_date ,
  proj.public_sector_flag ,
  proj.project_status_name ,
  proj.project_status_code ,
  proj.wf_status_code ,
  proj.country_name ,
  proj.country_code ,
  proj.record_version_number ,
  proj.target_start_date ,
  proj.scheduled_start_date ,
  proj.scheduled_finish_date ,
  proj.actual_start_date ,
  proj.actual_finish_date,
  opr.name operating_unit
FROM pa_projects_prm_v proj,
  hr_all_organization_units_vl opr
where proj.org_id   = opr.organization_id
AND proj.project_id = :p_project_id;


Project Parties (Project Key Members):


SELECT *
FROM
  (SELECT DISTINCT PPP.PROJECT_ID project_id,
    DECODE(PA.ASSIGNMENT_ID,NULL,PPRT.MEANING,PA.ASSIGNMENT_NAME) project_role_meaning,
    PPP.RESOURCE_SOURCE_ID resource_source_id,
    PE.FULL_NAME resource_source_name,
    PPP.PROJECT_ROLE_ID project_role_id,
    PPRT.PROJECT_ROLE_TYPE project_role_type,
    PPP.START_DATE_ACTIVE start_date_active,
    ppp.end_date_active end_date_active,
    pa_project_parties_utils.active_party(ppp.start_date_active,ppp.end_date_active) active,
    'EMPLOYEE' party_type
  FROM PA_PROJECT_PARTIES PPP,
    PA_PROJECTS_ALL PPA,
    PA_PROJECT_ROLE_TYPES PPRT,
    PER_ALL_PEOPLE_F PE,
    PA_PROJECT_ASSIGNMENTS PA,
    fnd_user u,
    (SELECT pj.name job_name,
      haou.organization_id org_id,
      haou.name org_name,
      paf.person_id,
      paf.assignment_type
    FROM per_all_assignments_f paf,
      per_jobs pj,
      hr_all_organization_units haou
    WHERE TRUNC(sysdate) BETWEEN TRUNC(paf.effective_start_date) AND TRUNC(paf.effective_end_date)
    AND paf.primary_flag     = 'Y'
    AND paf.organization_id  = haou.organization_id
    AND NVL(paf.job_id, -99) = pj.job_id(+)
    ) prd
  WHERE PPP.RESOURCE_TYPE_ID  = 101
  AND PPP.PROJECT_ID          = PPA.PROJECT_ID
  AND PPP.PROJECT_ROLE_ID     = PPRT.PROJECT_ROLE_ID
  AND PPP.RESOURCE_SOURCE_ID  = PE.PERSON_ID
  AND PE.EFFECTIVE_START_DATE =
    (SELECT MIN(PAPF.EFFECTIVE_START_DATE)
    FROM PER_ALL_PEOPLE_F PAPF
    WHERE PAPF.PERSON_ID         =PE.PERSON_ID
    AND PAPF.EFFECTIVE_END_DATE >= TRUNC(SYSDATE)
    )
  AND PE.EFFECTIVE_END_DATE          >=TRUNC(SYSDATE)
  AND PPP.PROJECT_PARTY_ID            = PA.PROJECT_PARTY_ID(+)
  AND NVL(prd.assignment_type,'-99') IN ('C',DECODE(DECODE(PE.CURRENT_EMPLOYEE_FLAG,'Y','Y', DECODE(PE.CURRENT_NPW_FLAG,'Y','Y','N')),'Y','E', 'B'),'E', '-99')
  AND ppp.resource_source_id          = prd.person_id(+)
  AND u.employee_id (+)               = ppp.resource_source_id
  AND ppp.object_type                 = 'PA_PROJECTS'
  AND ppp.object_id                   = ppa.project_id
  UNION ALL
  SELECT DISTINCT ppp.project_id,
    pprt.meaning,
    ppp.resource_source_id,
    hzp.party_name,
    ppp.project_role_id,
    pprt.project_role_type,
    ppp.start_date_active,
    ppp.end_date_active,
    pa_project_parties_utils.active_party(ppp.start_date_active,ppp.end_date_active),
    'PERSON'
  FROM pa_project_parties ppp,
    pa_projects_all ppa,
    pa_project_role_types pprt,
    hz_parties hzp,
    hz_parties hzo,
    hz_relationships hzr,
    hz_contact_points hzcp,
    fnd_user u
  WHERE ppp.resource_type_id     = 112
  AND ppp.project_id             = ppa.project_id
  AND ppp.project_role_id        = pprt.project_role_id
  AND ppp.resource_source_id     = hzp.party_id
  AND hzp.party_type             = 'PERSON'
  AND hzo.party_type             = 'ORGANIZATION'
  AND hzr.relationship_code     IN ('EMPLOYEE_OF', 'CONTACT_OF')
  AND hzr.status                 = 'A'
  AND hzr.subject_id             = hzp.party_id
  AND hzr.object_id              = hzo.party_id
  AND hzr.object_table_name      = 'HZ_PARTIES'
  AND hzr.directional_flag       = 'F'
  AND hzcp.owner_table_name (+)  = 'HZ_PARTIES'
  AND hzcp.owner_table_id (+)    = hzp.party_id
  AND hzcp.contact_point_type (+)= 'PHONE'
  AND hzcp.phone_line_type (+)   = 'GEN'
  AND hzcp.primary_flag (+)      = 'Y'
  AND u.person_party_id (+)      = ppp.resource_source_id
  AND ppp.object_type            = 'PA_PROJECTS'
  AND ppp.object_id              = ppa.project_id
  UNION ALL
  SELECT DISTINCT ppp.project_id,
    pprt.meaning,
    ppp.resource_source_id,
    hzo.party_name,
    ppp.project_role_id,
    pprt.project_role_type,
    ppp.start_date_active,
    ppp.end_date_active,
    pa_project_parties_utils.active_party(ppp.start_date_active,ppp.end_date_active),
    'ORGANIZATION'
  FROM pa_project_parties ppp,
    pa_projects_all ppa,
    pa_project_role_types_vl pprt,
    hz_parties hzo,
    hz_contact_points hzcp
  WHERE ppp.resource_type_id     = 112
  AND ppp.project_id             = ppa.project_id
  AND ppp.project_role_id        = pprt.project_role_id
  AND ppp.resource_source_id     = hzo.party_id
  AND hzo.party_type             = 'ORGANIZATION'
  AND hzcp.owner_table_name (+)  = 'HZ_PARTIES'
  AND hzcp.owner_table_id (+)    = hzo.party_id
  AND hzcp.contact_point_type (+)= 'PHONE'
  AND hzcp.phone_line_type (+)   = 'GEN'
  AND hzcp.primary_flag (+)      = 'Y'
  AND ppp.object_type            = 'PA_PROJECTS'
  AND ppp.object_id              = ppa.project_id
  UNION ALL
  SELECT ppc.project_id,
    'Customer Person' meaning,
    NULL,
    ppc.customer_name,
    NULL,
    NULL,
    NULL,
    NULL,
    DECODE(ppc.customer_status,'A','Y','I','N'),
    'ORGANIZATION' party_type
  FROM pa_project_customers_v ppc,
    hz_parties hzo,
    hz_contact_points hzcp
  WHERE hzcp.owner_table_name (+)= 'HZ_PARTIES'
  AND hzcp.owner_table_id (+)    = hzo.party_id
  AND hzcp.contact_point_type (+)= 'PHONE'
  AND hzcp.phone_line_type (+)   = 'GEN'
  AND hzcp.primary_flag (+)      = 'Y'
  AND ppc.party_type             ='PERSON'
  AND ppc.party_id               =hzo.party_id
  )
WHERE (project_id = :p_project_id
and party_type   <> 'ORGANIZATION'
AND TRUNC(sysdate) BETWEEN start_date_active AND NVL(end_date_active,TRUNC(sysdate)));


Approved Cost Budget Version Detail:

SELECT po.project_id,
  bv.version_name,
  pt.name AS plan_type_name,
  bv.description,
  po.fin_plan_preference_code,
  bv.budget_version_id,
  bv.record_version_number,
  bv.budget_status_code,
  bv.raw_cost Row_Cost_Total
FROM pa_proj_fp_options po,
  pa_fin_plan_types_vl pt,
  pa_budget_versions bv
WHERE bv.project_id      = :p_project_id
AND bv.budget_version_id =
  (SELECT budget_version_id
  from pa_budget_versions
  WHERE project_id    =:p_project_id
  AND fin_plan_type_id=
    (SELECT fin_plan_type_id
    FROM pa_fin_plan_types_vl
    WHERE migrated_frm_bdgt_typ_code='AC'
    AND UPPER(NAME)                 =UPPER('Approved Cost Budget')
    )
  AND budget_status_code='B'
  AND CURRENT_FLAG      ='Y'
  )
AND bv.budget_version_id          = po.fin_plan_version_id
AND bv.ci_id                     IS NULL
AND po.fin_plan_option_level_code = 'PLAN_VERSION'
AND po.fin_plan_type_id           = pt.fin_plan_type_id
AND pt.fin_plan_type_id           =
  (SELECT fin_plan_type_id
  FROM pa_fin_plan_types_vl
  WHERE migrated_frm_bdgt_typ_code='AC'
  and upper(name)                 =upper('Approved Cost Budget')
  );

Insert Run Report in Excel for Apps

Insert Run Report Feature in ExcelforApps

Insert Run functionality is used to return sum, count, max etc for the datapoint(columns) in E4A Report. The value will be returned in the cell once the calculation is performed. If the report is defined to run for the specific parameters, then the Insert Run report calculation can also be made to run for that parameter.

How to Create a Insert Run Report

Go to Excel4Apps Responsibility and click Insert Run Report

List of Reports will be displayed. Select the Report where we need to create Insert Run functionality.

For example if we need to count the number of Employees in the report, Select the Report output and click on any cell in the header section. Then Click “Insert Run Report”. The below screen will be displayed

Now click Next and then add the Filters required for the Count Operation to be manipulated. Users can enter upto 10 filters. The filters will also be applied as in the report or also for the parameters not defined in the report.

Add the filters and then Click Finish. The formula will be calculated and displayed in the cell.

How to Use Associative array in select statement as normal database table

Associative array in select statement as normal database table

Step 1

First Create Associative Array Type

TYPE  assoc_array_type  IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;


if required We can use as Cursor parameter but this is optional


function or procedure_name (Parameter1   datatype)

DECLARE  SECTION

assoc_var  assoc_array_type;


CURSOR  invoice_cur(cur_para_assocv  assoc_array_type) IS
SELECT DISTINCT
   trx_head.trx_number invoice_number,
   cust_acct.cust_account_id,
   SUBSTR(cust_acct.account_number,7,6) customer_number,
   cust_acct.account_name,
   loc.address1,
   loc.address2,
   loc.address3,
   loc.address4,
   loc.city,
   loc.county,
   loc.state,
   loc.postal_code,
   loc.country,
   term.NAME payment_term,
   trx_head.term_id term_id,
   trx_head.cust_trx_type_id trx_type_id,
   trx_head.bill_to_site_use_id,
   trx_head.bill_to_contact_id,
   party_sites.party_id,
   cust_acct.account_number
FROM
   ra_customer_trx           trx_head,
   ra_cust_Trx_types         trx_types,
   hz_locations              loc,
   ra_terms                  term,
   hz_cust_accounts          cust_acct,
   hz_party_sites            party_sites,
   hz_cust_acct_sites        cust_acct_sites,
   hz_cust_site_uses         cust_site_uses
WHERE cust_acct.cust_account_id = trx_head.bill_to_customer_id
   AND term.term_id = trx_head.term_id
   AND trx_head.cust_trx_type_id = trx_types.cust_trx_type_id
   AND loc.location_id = party_sites.location_id
   AND party_sites.party_site_id = cust_acct_sites.party_site_id
   AND cust_acct_sites.cust_acct_site_id = cust_site_uses.cust_acct_site_id
   AND cust_acct_sites.cust_account_id = cust_acct.cust_account_id
   AND cust_site_uses.site_use_id = trx_head.bill_to_site_use_id
   AND term.due_cutoff_day IS NULL
   AND trx_head.complete_flag = 'Y'
   AND trx_head.printing_option = 'PRI'
   AND trx_head.bill_to_customer_id IS NOT NULL
   AND cust_acct_sites.attribute9 IS NOT NULL
   AND cust_acct_sites.attribute9 IN (SELECT * FROM TABLE(cur_para_assocv))
-- The above show the associative array used as normal Table.


BEGIN  SECTION
..................... IF SOME STATEMENTS REQUIRED YOU CAN USE  HERE



if  Parameter1 ='REST ALL' then
   select distinct attribute9 BULK COLLECT INTO assoc_var from apps.hz_cust_acct_sites_all where org_id=apps.FND_PROFILE.VALUE('ORG_ID') and (ATTRIBUTE9 NOT LIKE 'XXX' AND ATTRIBUTE9 NOT LIKE 'YYY' AND ATTRIBUTE9 NOT LIKE 'ZZZ' ) ;
 elsif  Parameter1 ='XXX'   then
  select distinct attribute9 BULK COLLECT INTO assoc_var from apps.hz_cust_acct_sites_all where org_id=apps.FND_PROFILE.VALUE('ORG_ID') and ATTRIBUTE9  LIKE 'XXX%' ;
 elsif  Parameter1 ='YYY' THEN
    select distinct attribute9 BULK COLLECT INTO assoc_var from apps.hz_cust_acct_sites_all where org_id=apps.FND_PROFILE.VALUE('ORG_ID') and ATTRIBUTE9  LIKE 'YYY%' ;
 elsif  Parameter1='ZZZ' then
    select distinct attribute9 BULK COLLECT INTO assoc_var from apps.hz_cust_acct_sites_all where org_id=apps.FND_PROFILE.VALUE('ORG_ID') and ATTRIBUTE9  LIKE 'ZZZ%' ;
 else
    select distinct attribute9 BULK COLLECT INTO assoc_var  from apps.hz_cust_acct_sites_all where org_id=apps.FND_PROFILE.VALUE('ORG_ID');
 end if;


FOR invoice_rec IN invoice_cur (assoc_var )
  LOOP

...................
...................SOME STATEMENTS HERE

END LOOP;

END ;

File Moving and renaming using UNIX SHELL Script in EBS

#!/bin/bash
#################################################################################
# File_Name    : imauanzstmtfilearchv.prog                                                                            #
# DESCRIPTION  : File Archiving using Shell programming(HOST)                                     #
#              :                                                                                                                           #
# PROCESS FLOW : After processing a data file We can use this program                          #
#                  to move the processed file to  archive directory                                                 #
#                                                                                                                                          #
#  Parameters  :  $5 = SOURCE_DIR_PATH                                                                       #
#                 $6 = SOURCE_FILE_NAME                                                                          #
#                 $7 = ARCHIVE_DIR_PATH                                                                            #
#                 $8 = ARCHIVE_FILE_NAME                                                                         #
#                 $9 = ARCH_PATH                                                                                            #
# =====================================================================      #
# CREATE/MODIFICATION HISTORY                                                                             #
# ===========+====================+====================================      #
# DATE       |NAME                |DETAILS                                                                             #
# ===========+====================+====================================      #
#################################################################################

TODAY=$(date)
HOST=$(hostname)
echo "---------------------------------------------------------------------"
echo " Started Date, Time : $TODAY "
echo " Host:$HOST "
echo "---------------------------------------------------------------------"
echo "====================================================================="
echo " Program To MOVE a file from one dir to other dir"
echo "====================================================================="
#######################################################################

# Standard parameters
APPS_LOGIN=$1
USER_ID=$2
USER_NAME=$3
REQ_ID=$4
SOURCE_DIR=/interface/servername/INSTANCE/outgoing/COUNTRY
DESTINATION_DIR=/interface/server/INSTANCE/outgoing/COUNTRY/ARCHIVE

# Custom parameters
SOURCE_DIR_PATH=$5
SOURCE_FILE_NAME=$6
ARCHIVE_DIR_PATH=$7
ARCHIVE_FILE_NAME=$8
echo `pwd`

#Display Parameters
echo Request ID is            $REQ_ID
echo User Name         $USER_NAME
echo User id    $USER_ID
echo Source Directory Path is   $SOURCE_DIR_PATH
echo Source File Name           $SOURCE_FILE_NAME
echo Archive Directory Path is  $ARCHIVE_DIR_PATH
echo Archived File Name         $ARCHIVE_FILE_NAME
cd $SOURCE_DIR
echo `pwd`
if [ -f $SOURCE_DIR_PATH/$SOURCE_FILE_NAME ]
then
mv $SOURCE_DIR_PATH/$SOURCE_FILE_NAME $ARCHIVE_DIR_PATH/$ARCHIVE_FILE_NAME
# rm $SOURCE_DIR_PATH/$SOURCE_FILE_NAME
echo Source file  $SOURCE_DIR_PATH/$SOURCE_FILE_NAME has been moved to $ARCHIVE_DIR_PATH/$ARCHIVE_FILE_NAME
else
echo " Source File Archiving Failed, File NOT FOUND, Check DIR or File name "
fi
echo "********    Host Program Execution completed    *************"

#  end of script

ORACLE UTL_SMTP MAIL

How to Send SMTP MAIL to Multiple Recipents using ORACLE UTL_SMTP package
------------------------------------------------------------------------------------------
Below procedure shows how to send email to mutliple recipents

CREATE OR REPLACE PROCEDURE XXCUST.XXX_SMTP_MAIL_NOTIF_PRC(errbuf  OUT VARCHAR2,
                                                            retcode OUT NUMBER,
                                                            p_smtp_server IN VARCHAR2 default 'mail.yourcompany.com', -- ask your dba for this server name and port
                                                            p_smtp_port   IN VARCHAR2 default '25',
                                                            p_mail_sender  IN VARCHAR2  default 'donotreply@doyensys.com' )
IS                                                           
p_subject       varchar2(1000);
l_conn          utl_smtp.connection;
l_row_found     boolean := false ;
l_mesg            varchar2(32767);
l_db_name         varchar2(10) := substr(sys.database_name,1,7); -- database name
c_mail_boundary   constant varchar2(255) default '--ABCDEFGHIJKL12345--';
c_mail_footer     constant varchar2(1000) := '<p><b>Note:</b> For any Help please raise IT ticket to Support team.<br> ************* This is auto generated email from the system. Please do not reply *************</p></body></html>';
crlf              constant varchar2(2):= chr(13) || chr(10);

TYPE email_list IS TABLE OF VARCHAR2(240); -- collection type definition

rec_list  email_list; -- collection variable to hold multiple recipients


BEGIN

p_subject   :='Email Subject Here';


select  description bulk collect into rec_list  from ar_lookups where lookup_type='NOTIF_EMAIL_LIST' and ENABLED_FLAG='Y' ; -- for getting recipients email id s from database table



  FOR i in (
            select col1,col2,col3,col4,col5 -- if you have email body text from database table
            FROM  table1,table2
            WHERE table1.col6=table2.col5  --joing conditions here
        )
  LOOP
    if not l_row_found then    -- this will execute one time for each procedure call 
            l_conn := utl_smtp.open_connection (host => p_smtp_server, port => p_smtp_port ) ;
            utl_smtp.helo(l_conn, p_smtp_server);
            utl_smtp.mail(l_conn, p_mail_sender );
            --utl_smtp.rcpt(l_conn, p_recip);
         
            l_mesg := l_mesg ||'Date: '|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') ||crlf;
            l_mesg := l_mesg ||'To: ';
         
            for i in rec_list.FIRST .. rec_list.last loop -- if you have mutliple email recipients then you can use this for loop
                utl_smtp.Rcpt(l_conn,rec_list(i));
                if i < rec_list.last then
               l_mesg := l_mesg ||rec_list(i)||',';
               else
               l_mesg := l_mesg ||rec_list(i)||crlf;
               end if;
             
            end loop;
         
         
            l_mesg := l_mesg ||'From: '|| p_mail_sender||crlf;
            l_mesg := l_mesg ||'Subject: '||p_subject||'-'||l_db_name ||crlf;
            -- l_mesg := l_mesg || 'Reply-To: ' || c_mail_from  ||  crlf;
            l_mesg := l_mesg ||'MIME-Version: 1.0'|| crlf;
            l_mesg := l_mesg || 'Content-Type: multipart/alternative; boundary=' ||chr(34) || c_mail_boundary ||  chr(34) || crlf;       
            l_mesg := l_mesg || 'content-type: text/html;' || crlf || crlf;
            l_mesg := l_mesg || '<html><head><title>'||p_subject||'</title></head><body>';
            utl_smtp.open_data(l_conn);
            utl_smtp.write_data(l_conn,l_mesg);
            utl_smtp.write_data(l_conn,'<p> This is to notify that the Dearchive program is completed and below is the summary.<br></p>');
            utl_smtp.write_data(l_conn,'<table cellspacing="0" cellpadding="0" border="1">');
            utl_smtp.write_data(l_conn,'<tr bgcolor="BLUE">'||
                                              '<td width="200"><font face="Calibri">'||'<b>Col_HDR1</b>'||
                                              '<td width="100" align="center"><font face="Calibri">'||'<b>Col_HDR2</b>'||
                                              '<td width="180" align="center"><font face="Calibri">'||'<b>Col_HDR3</b>'||
                                              '<td width="300" align="center"><font face="Calibri">'||'<b>Col_HDR4</b>'||
                                              '<td width="400"><font face="Calibri">'||'<b>Col_HDR5</b>'||
                                      '</tr>'||crlf
                            );
            l_row_found := true;
    end if;
            utl_smtp.write_data(l_conn,'<tr>'||
                                              '<td width="200"><font face="Calibri">'||i.col1||
                                              '<td width="100" align="center"><font face="Calibri">'||i.col2||
                                              '<td width="180" align="center"><font face="Calibri">'||i.col3||
                                              '<td width="300"  align="center"><font face="Calibri">'||i.col4||
                                              '<td width="400"><font face="Calibri">'||i.col5||
                                       '</tr>'||crlf
                        );
  end loop;

  if l_row_found then
    utl_smtp.write_data(l_conn,'</table> ');
    if p_mail_footer is null then   
    utl_smtp.write_data(l_conn,c_mail_footer);
    else
    utl_smtp.write_data(l_conn,p_mail_footer);
    end if;
    utl_smtp.close_data(l_conn);
    utl_smtp.quit(l_conn);
   end if;
 
    --DBMS_OUTPUT.PUT_LINE('At END');
exception
  when others then
  retcode:=2;
  errbuf:='error in procedure';
  FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error occured while running this program, please check LOG file for Error Details');
  FND_FILE.PUT_LINE(FND_FILE.LOG,' Exception Occured , SQL ERROR MESSAGE :'||SQLERRM);
    begin utl_smtp.quit(l_conn);
    exception when others then
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception Occured While utl_smtp.quit()  , SQL ERROR MESSAGE :'||SQLERRM);
    end;

end XXX_SMTP_MAIL_NOTIF_PRC;
/

Fusion HCM - Customizing the Global Cheque

1. Copy the Existing Check Template from the Location : 

2. Paste the Copied Check Template to the Custom Folder

3. Edit the Template according to your Requirement

4. Login to Application with implementation user and click Data Exchange in Navigator

5. Click ‘Manage HCM Extract Definitions’ link

6. In the search region enter ‘Payments’ for the name, select ‘Payments’ for the Type and select the Legislative Data Group (LDG) for which you want to create/customize the Check Template, and then click the Search button.

7. Click on Payments. The Edit Extract Definition page will open. Click Extract Delivery Options and click Add in Extract Delivery Options section.  Create a record for your Custom Check 

8. Add the Following Details :

·         Start date

·         End Date

·         Delivery Option Name (In example, name is ‘AH Check Writer Report’)

·         Output Type

·         Report (Report was created in /Custom/Human Capital Management/Payroll/Payment Distribution folder and report name was AHChequeReport, enter complete path as /Custom/Human Capital Management/Payroll/Payment Distribution/AHChequeReport.xdo)

·         Output Name

·         Template (Name should match the layout name in the report created in BIP. It was created with name ‘AH Check Writer Report’)

·         Delivery Type: - None

·         Overriding Delivery Mode: - ‘US Check Writer Report’

·         Locale: - en-US.  It can be different depending upon the language of the report

9. In Additional Details (the section after Report categories), add an entry for the delivery option name for Standard Cheque.  Here, the Delivery option name was given as ‘AH Check Writer Report’

Fusion HCM - Customizing the Global Payslip

1. Copy the Existing Pay slip Template & Sub Template from the Location : Human Capital Management -> Payroll -> Payment Distribution

2. Paste the Copied Payslip Template to the Custom Folder

3. Edit the Template according to your Requirement

4. Login to Application with implementation user and click Data Exchange in Navigator

5. Click ‘Manage HCM Extract Definitions’ link

6. In the search region enter ‘Payslip’ for the name, select ‘Archive Retrieval’ for the Type and select the Legislative Data Group (LDG) for which you want to create/customize the Payslip, and then click the Search button

7. Click on Payslip. The Edit Extract Definition page will open. Click Extract Delivery Options and click Add in Extract Delivery Options section.  Create a record for your custom Payslip

8. Add the Following Details :

·         Start date

·         End Date

·         Delivery Option Name (In example, name is ‘AH Online Payslip’)

·         Output Type

·         Report (Report was created in /Custom/Human Capital Management/Payroll/Payment Distribution folder and report name was AH_USOnlinePayslip, enter complete path as /Custom/Human Capital Management/Payroll/Payment Distribution/AH_USOnlinePayslip.xdo)

·         Output Name

·         Template (Name should match the layout name in the report created in BIP. It was created with name ‘AH_USOnlinePayslip’)

·         Delivery Type: - ‘Document of Records’

·         Bursting Node: - ‘/DATA_DS/G_1/FILE_FRAGMENT/PAYSLIP/PAYMENT_RECORD/REL_ACTION_ID’

·         Overriding Delivery Mode: - ‘US Online Payslip’

9. In the ‘Additional Details’ region, add details similar to ‘Online Payslip’ Record that is already provided by oracle :

·         Date From :-
/DATA_DS/G_1/FILE_FRAGMENT/PAYSLIP/PAYMENT_RECORD/GLB_ARCH_PR/GLB_PAY_ARCH_PR_PAYROLL/PAYROLL_PERIOD_START_DATE

·         Date To:-
/DATA_DS/G_1/FILE_FRAGMENT/PAYSLIP/PAYMENT_RECORD/GLB_ARCH_PR/GLB_PAY_ARCH_PR_PAYROLL/PAYROLL_PERIOD_END_DATE

·         Information Text1:-
/DATA_DS/G_1/FILE_FRAGMENT/PAYSLIP/PAYMENT_RECORD/GLB_ARCH_PR/GLB_PAY_ARCH_EE_INFO/EMAIL_ADDRESS

·         Information Category:- GLB_PAYSLIP

·         Information Date1:-
/DATA_DS/G_1/FILE_FRAGMENT/PAYSLIP/PAYMENT_RECORD/GLB_ARCH_PR/GLB_PAY_ARCH_PR_PAYROLL/PAYROLL_PAYSLIP_VIEW_DATE

·         Information Number1:-
/DATA_DS/G_1/FILE_FRAGMENT/PAYSLIP/PAYMENT_RECORD/GLB_ARCH_PR/GLB_PAY_ARCH_PR_PAYROLL/PAYROLL_ID

·         Information Number2:-
/DATA_DS/G_1/FILE_FRAGMENT/PAYSLIP/PAYMENT_RECORD/GLB_ARCH_PR/GLB_PAY_ARCH_PR_PAYROLL/PAYROLL_PERIOD_NUMBER

·         Information Number3:-
/DATA_DS/G_1/FILE_FRAGMENT/PAYSLIP/PAYMENT_RECORD/NET_PAY

·         Information Number5:-
/DATA_DS/G_1/FILE_FRAGMENT/PAYSLIP/PAYMENT_RECORD/GLB_ARCH_PR/GLB_PAY_ARCH_PR_INFO/GLB_ARCH_CBID/GLB_PAY_ARCH_CBID_INFORMATION/PREPAYMENT_RELATIONSHIP_ACTION_ID

·         Information Number6:-
/DATA_DS/G_1/FILE_FRAGMENT/PAYSLIP/PAYMENT_RECORD/GLB_ARCH_PR/GLB_PAY_ARCH_PR_INFO/GLB_ARCH_CBID/GLB_PAY_ARCH_CBID_INFORMATION/CALC_BREAKDOWN_ID

·         Document Name:- PAYSLIP

·         Issued Date:-
/DATA_DS/G_1/FILE_FRAGMENT/PAYSLIP/PAYMENT_RECORD/PAYMENT_DETAILS/PAYMENT_DATE

·         Key: - Calculation Breakdown Identifier

·         Locale: - en-US.  It can be different depending upon the language of the report

·         Person Id: -
/DATA_DS/G_1/FILE_FRAGMENT/PAYSLIP/PAYMENT_RECORD/GLB_ARCH_PR/GLB_PAY_ARCH_EE_INFO/PERSON_ID

·         Related Object Id: -
/DATA_DS/G_1/FILE_FRAGMENT/PAYSLIP/PAYMENT_RECORD/REL_ACTION_ID

·         Related Object Id Col: - PAYROLL_REL_ACTION_ID

·         Related Object Name: -  PAY_PAYROLL_REL_ACTIONS

·         System Document Type: -  GLB_PAYSLIP

10. In Additional Details (the section after Report categories), add an entry for the delivery option name for your report.  Here, the Delivery option name was given as ‘AH Online Payslip’

Fusion HCM - Query for Payroll Costing Details

SELECT paaf.assignment_number, petf.base_element_name element_name,
       (SELECT petf2.base_element_name
          FROM pay_input_values_f piv2,
               pay_element_types_f petf2
         WHERE piv2.element_type_id = petf2.element_type_id
           AND pc.distributed_input_value_id = piv2.input_value_id
           AND ppa.effective_date BETWEEN petf2.effective_start_date
                                      AND petf2.effective_end_date
           AND ppa.effective_date BETWEEN piv2.effective_start_date
                                      AND piv2.effective_end_date
           AND ROWNUM < 2) distributed_element,
       DECODE (pc.debit_or_credit, 'D', 1, 0) * pc.costed_value AS debit,
       DECODE (pc.debit_or_credit, 'C', 1, 0) * pc.costed_value AS credit,
       DECODE (pc.balance_or_cost,
               'B', 'Balance',
               'C', 'Cost'
              ) AS balance_or_cost,
       ppa.effective_date, pcak.*
  FROM pay_payroll_actions ppa,
       pay_time_periods ptp,
       pay_costs pc,
       pay_run_results prr,
       pay_input_values_vl piv,
       pay_cost_alloc_keyflex pcak,
       pay_pay_relationships_dn prd,
       pay_all_payrolls_f pap,
       pay_element_types_f petf,
       pay_ele_classifications_tl pectl,
       pay_payroll_rel_actions pra,
       hcm_lookups uomlookup,
       pay_rel_groups_dn payrel,
       pay_assigned_payrolls_dn papd,
       per_all_assignments_f paaf,
       per_legal_employers ple
 WHERE ppa.legislative_data_group_id = (SELECT legislative_data_group_id
                                          FROM per_legislative_data_groups_vl
                                         WHERE NAME = 'US LDG')
   AND pc.payroll_rel_action_id = pra.payroll_rel_action_id
   AND prd.payroll_relationship_id = pra.payroll_relationship_id
   AND pra.payroll_relationship_id = payrel.payroll_relationship_id
   AND payrel.assignment_id = paaf.assignment_id
   AND payrel.group_type = 'A'
   AND payrel.parent_rel_group_id = papd.payroll_term_id
   AND ppa.effective_date BETWEEN payrel.start_date AND payrel.end_date
   AND papd.payroll_id = pap.payroll_id
   AND ppa.effective_date BETWEEN papd.start_date AND papd.end_date
   AND ppa.effective_date BETWEEN paaf.effective_start_date
                              AND paaf.effective_end_date
   AND paaf.legal_entity_id = ple.organization_id
   AND ple.status = 'A'
   AND ppa.effective_date BETWEEN ple.effective_start_date
                              AND ple.effective_end_date
   AND pra.chunk_number IS NOT NULL
   AND ppa.payroll_action_id = pra.payroll_action_id
   AND ppa.effective_date BETWEEN prd.start_date AND prd.end_date
   AND pra.action_status = 'C'
   AND ppa.action_type IN ('B', 'C', 'CA', 'EC', 'Q', 'R', 'S', 'V')
   AND ppa.payroll_id = pap.payroll_id
   AND ppa.effective_date BETWEEN pap.effective_start_date
                              AND pap.effective_end_date
   AND ppa.effective_date BETWEEN prd.start_date AND prd.end_date
   AND TRUNC (ppa.effective_date) BETWEEN NVL (:paydate,
                                               TRUNC (ppa.effective_date)
                                              )
                                      AND NVL (:paydate,
                                               TRUNC (ppa.effective_date)
                                              )
   AND ppa.earn_time_period_id = ptp.time_period_id(+)
   AND prr.run_result_id = pc.run_result_id
   AND prr.element_type_id = petf.element_type_id
   AND pectl.classification_id = petf.classification_id
   AND ppa.effective_date BETWEEN petf.effective_start_date
                              AND petf.effective_end_date
   AND pc.input_value_id = piv.input_value_id
   AND uomlookup.lookup_code = piv.uom
   AND uomlookup.lookup_type = 'PAY_UNITS'
   AND pc.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id